﻿Imports System.Data.SqlClient
Imports Crystal.CLL
Imports CrystalDataHub.BLL.DataHub.BusinessObject
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common
Imports CrystalDataHub.BLL
Imports Crystal.Common.Model
Imports System.Transactions

Public Class DataHubManager
    Implements IDisposable

#Region "Enum"

    Public Enum NotesSaveType
        AsItIs = 1
        Append = 2
    End Enum

#End Region

#Region "Methods"
    Public Sub Dispose() Implements IDisposable.Dispose
    End Sub

    Public Sub GetData(ByVal pintProjectID As Integer, ByRef pintDataSourcesCount As Integer, ByRef pintPreviousProjectID As Integer, _
                            ByRef pstrPPDeliveryDate As String, ByVal pblnHasSnapshot As Boolean, ByRef pdtData As DataTable, _
                            ByRef pstCurrentDataSources As IList(Of CurrentDataSources), ByRef pstPreviousDataSources As IList(Of PreviousDataSources), _
                            ByRef pstPreviousProjects As IList(Of PreviousProjects), ByRef pstrDeliveryListingToSort As String)
        Dim lobjCurrentDataSources As CurrentDataSources = Nothing
        Dim lobjPreviousDataSources As PreviousDataSources = Nothing
        Dim lobjPreviousProjects As PreviousProjects = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_DE_GetFullData")
                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
                lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
                lentDB.AddInParameter(cmd, "HasSnapshot", DbType.Boolean, pblnHasSnapshot)
                cmd.CommandTimeout = 0
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    If pblnHasSnapshot = False Then
                        pdtData = New DataTable
                        pdtData.Load(reader, LoadOption.OverwriteChanges)
                    End If

                    Dim lintListingIDIndex As Int16 = reader.GetOrdinal("ListingID")
                    Dim lintListingNameIndex As Int16 = reader.GetOrdinal("ListingName")
                    Dim lintEntityIDIndex As Int16 = reader.GetOrdinal("EntityID")
                    Dim lintESourceIDIndex As Int16 = reader.GetOrdinal("ESourceID")
                    Dim lintESourceNameIndex As Int16 = reader.GetOrdinal("ESourceName")
                    Dim lintSourceNameIndex As Int16 = reader.GetOrdinal("SourceName")
                    Dim lintSourceTypeCodeIndex As Int16 = reader.GetOrdinal("SourceTypeCode")
                    Dim lintSourceDateIndex As Int16 = reader.GetOrdinal("SourceDate")
                    Dim lintAccessStatusIndex As Int16 = reader.GetOrdinal("AccessStatus")
                    Dim lintHasAnyDoubtsIndex As Int16 = reader.GetOrdinal("HasAnyDoubts")
                    Dim lintLastUserIDIndex As Int16 = reader.GetOrdinal("LastUserID")
                    Dim lintESourceCodeIndex As Int16 = reader.GetOrdinal("ESourceCode")
                    Dim lintPublicFilingDateColIndex As Int16 = reader.GetOrdinal("PublicFilingDateCol")
                    Dim lintPublicSourceColIndex As Int16 = reader.GetOrdinal("PublicSourceCol")
                    Dim lintResearchSourceColIndex As Int16 = reader.GetOrdinal("ResearchSourceCol")
                    Dim lintIsNDIndex As Int16 = reader.GetOrdinal("IsND")
                    Dim lintIsNilIndex As Int16 = reader.GetOrdinal("IsNil")
                    While reader.Read()
                        lobjCurrentDataSources = New CurrentDataSources() With { _
                         .ListingID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintListingIDIndex)), _
                         .ListingName = DBParseUtils.GetFieldValueString(reader.GetString(lintListingNameIndex)), _
                         .EntityID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintEntityIDIndex)), _
                         .ESourceID = DBParseUtils.GetFieldValueByte(reader.GetByte(lintESourceIDIndex)), _
                         .ESourceName = DBParseUtils.GetValueString(reader.GetString(lintESourceNameIndex)), _
                         .SourceName = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceNameIndex)), _
                         .SourceTypeCode = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceTypeCodeIndex)), _
                         .SourceDate = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceDateIndex)), _
                         .AccessStatus = DBParseUtils.GetFieldValueString(reader.GetString(lintAccessStatusIndex)), _
                         .HasAnyDoubts = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintHasAnyDoubtsIndex)), _
                         .LastUserID = DBParseUtils.GetFieldValueInt16(reader.GetInt16(lintLastUserIDIndex)), _
                         .ESourceCode = DBParseUtils.GetFieldValueString(reader.GetString(lintESourceCodeIndex)), _
                         .PublicFilingDateCol = DBParseUtils.GetFieldValueString(reader.GetString(lintPublicFilingDateColIndex)), _
                         .PublicSourceCol = DBParseUtils.GetFieldValueString(reader.GetString(lintPublicSourceColIndex)), _
                         .ResearchSourceCol = DBParseUtils.GetFieldValueString(reader.GetString(lintResearchSourceColIndex)), _
                         .IsND = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintIsNDIndex)), _
                         .IsNIL = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintIsNilIndex)) _
                        }
                        pstCurrentDataSources.Add(lobjCurrentDataSources)
                    End While

                    reader.NextResult()

                    lintESourceNameIndex = reader.GetOrdinal("ESourceName")
                    lintSourceNameIndex = reader.GetOrdinal("SourceName")
                    lintSourceDateIndex = reader.GetOrdinal("SourceDate")
                    lintListingNameIndex = reader.GetOrdinal("ListingName")
                    lintSourceTypeCodeIndex = reader.GetOrdinal("SourceTypeCode")

                    While reader.Read()
                        lobjPreviousDataSources = New PreviousDataSources() With { _
                         .ESourceName = DBParseUtils.GetValueString(reader.GetString(lintESourceNameIndex)), _
                         .SourceName = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceNameIndex)), _
                         .SourceDate = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceDateIndex)), _
                         .ListingName = DBParseUtils.GetFieldValueString(reader.GetString(lintListingNameIndex)), _
                         .SourceTypeCode = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceTypeCodeIndex)) _
                         }
                        pstPreviousDataSources.Add(lobjPreviousDataSources)
                    End While

                    reader.NextResult()

                    Dim lintProjectIDIndex As Int16 = reader.GetOrdinal("ProjectID")
                    lintListingIDIndex = reader.GetOrdinal("ListingID")
                    Dim lintDeliveryListingIndex As Int16 = reader.GetOrdinal("DeliveryListing")

                    While reader.Read()
                        lobjPreviousProjects = New PreviousProjects() With { _
                         .ProjectID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintProjectIDIndex)), _
                         .ListingID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintListingIDIndex)), _
                         .DeliveryListing = DBParseUtils.GetFieldValueString(reader.GetString(lintDeliveryListingIndex)) _
                        }
                        pstPreviousProjects.Add(lobjPreviousProjects)
                    End While

                    reader.NextResult()
                    While reader.Read()
                        pintDataSourcesCount = reader.GetInt32(0)
                        pintPreviousProjectID = reader.GetInt32(1)
                        pstrPPDeliveryDate = reader.GetString(2)
                        pstrDeliveryListingToSort = reader.GetString(3)
                    End While
                End Using
            End Using
        Finally
            lobjCurrentDataSources = Nothing
            lobjPreviousDataSources = Nothing
            lobjPreviousProjects = Nothing
            lentDB = Nothing
        End Try
    End Sub

    'Public Sub ManageData(ByVal pstrDataSourceChanges As String, ByVal pstrPreviousProjectChanges As String, ByVal pstrLinkingChanges As String, _
    '                      ByVal pintProjectID As Integer, ByRef penmResult As DBResult, ByRef pstrErrorMessage As String)
    '    'Return DataFactory.CreateDataBase().ExecuteReader("CR_sp_EDS_ImportESource", New Object() {pintEntityID, pintListingID, pintProjectID, pintETemplateID, pintClientID, penmSourceTypeCode})
    '    Dim cmd As New SqlCommand
    '    Dim sqlParam(6) As SqlParameter
    '    'Dim ldtResults As DataTable
    '    'Dim ldsResults As DataSet = Nothing
    '    cmd.CommandTimeout = 0
    '    cmd.CommandType = CommandType.StoredProcedure
    '    cmd.CommandText = "CR_sp_DH_ManageData"

    '    sqlParam(0) = New SqlParameter("@DataSourceChanges", pstrDataSourceChanges)
    '    cmd.Parameters.Add(sqlParam(0))
    '    sqlParam(1) = New SqlParameter("@PreviousProjectChanges", pstrPreviousProjectChanges)
    '    cmd.Parameters.Add(sqlParam(1))
    '    sqlParam(2) = New SqlParameter("@LinkingChanges", pstrLinkingChanges)
    '    cmd.Parameters.Add(sqlParam(2))
    '    sqlParam(3) = New SqlParameter("@ProjectID", pintProjectID)
    '    cmd.Parameters.Add(sqlParam(3))
    '    sqlParam(4) = New SqlParameter("@UserID", CommonCLL.UserID)
    '    cmd.Parameters.Add(sqlParam(4))
    '    sqlParam(5) = New SqlParameter("@Result", SqlDbType.Int)
    '    sqlParam(5).Direction = ParameterDirection.Output
    '    cmd.Parameters.Add(sqlParam(5))
    '    sqlParam(6) = New SqlParameter("@ErrorMessage", SqlDbType.VarChar)
    '    sqlParam(6).Size = 2000
    '    sqlParam(6).Direction = ParameterDirection.Output
    '    cmd.Parameters.Add(sqlParam(6))

    '    CreateDataBase.ExecuteNonQuery(cmd)
    '    'ldsResults = CreateDataBase.ExecuteDataSet(cmd)
    '    'ldsResults = New DataSet
    '    'ldsResults = CreateDataBase.ExecuteDataSet(cmd)
    '    'If ldsResults.Tables.Count > 0 Then
    '    '    ldtResults = ldsResults.Tables(0)
    '    'End If
    '    If (cmd.Parameters(5).Value IsNot DBNull.Value) Then
    '        penmResult = CInt(cmd.Parameters(5).Value)
    '    End If
    '    If (cmd.Parameters(6).Value IsNot DBNull.Value) Then
    '        pstrErrorMessage = cmd.Parameters(6).Value.ToString
    '    End If
    '    'Return ldsResults
    'End Sub

    Public Sub ManageData(ByVal pstrDataSourceChanges As String, ByVal pstrPreviousProjectChanges As String, ByVal pstrLinkingChanges As String, _
                          ByVal pintProjectID As Integer, ByRef penmResult As DBResult, ByRef pstrErrorMessage As String, ByRef lstLinkingDetails As List(Of LinkingDetails), _
                          ByVal pblnIsFromDE As Boolean)
        Dim lobjLinkingDetails As LinkingDetails = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_DH_ManageData")
                lentDB.AddInParameter(cmd, "DataSourceChanges", DbType.String, pstrDataSourceChanges)
                lentDB.AddInParameter(cmd, "PreviousProjectChanges", DbType.String, pstrPreviousProjectChanges)
                lentDB.AddInParameter(cmd, "LinkingChanges", DbType.String, pstrLinkingChanges)
                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
                lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
                lentDB.AddInParameter(cmd, "IsFromDE", DbType.Boolean, pblnIsFromDE)
                cmd.CommandTimeout = 0
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
                    Dim lintFundIDIndex As Int16 = reader.GetOrdinal("FundID")
                    Dim lintLinkIDIndex As Int16 = reader.GetOrdinal("LinkID")
                    lstLinkingDetails = New List(Of LinkingDetails)
                    While reader.Read()
                        lobjLinkingDetails = New LinkingDetails() With { _
                         .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex)), _
                         .FundID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundIDIndex)), _
                         .LinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintLinkIDIndex)) _
                        }
                        lstLinkingDetails.Add(lobjLinkingDetails)
                    End While
                    reader.NextResult()
                    While reader.Read()
                        penmResult = CType(DBParseUtils.GetFieldValueInt32(reader.GetInt32(0)), DBResult)
                        pstrErrorMessage = DBParseUtils.GetFieldValueString(reader.GetString(1))
                    End While
                End Using
            End Using
        Finally
            lobjLinkingDetails = Nothing
        End Try
    End Sub

    Public Sub RecallDataChanges(ByVal pintProjectID As Integer, ByRef lstRecallData As IList(Of RecallData), ByRef lstRecallAddResults As IList(Of InvestorFundResults), _
                                 ByVal lstPreviousProjectRecallData As IList(Of PreviousProjectRecallData))
        Dim lobjRecallData As RecallData = Nothing
        Dim lobjPreviousProjectRecallData As PreviousProjectRecallData = Nothing
        Dim lobjRecallAddResults As InvestorFundResults = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            Using cmd As DbCommand = lentDB.GetStoredProcCommand("CR_sp_DE_RecallDataChanges")
                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
                lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
                    Dim lintOldInvestorIDIndex As Int16 = reader.GetOrdinal("OldInvestorID")
                    Dim lintNewFundIDIndex As Int16 = reader.GetOrdinal("NewFundID")
                    Dim lintNewLinkIDIndex As Int16 = reader.GetOrdinal("NewLinkID")
                    Dim lintOldLinkIDIndex As Int16 = reader.GetOrdinal("OldLinkID")
                    Dim lintOldFundIDIndex As Int16 = reader.GetOrdinal("OldFundID")
                    Dim lintFlagChangeTypeCodeIndex As Int16 = reader.GetOrdinal("FlagChangeTypeCode")
                    Dim lintIsHandledIndex As Int16 = reader.GetOrdinal("IsHandled")
                    While reader.Read()
                        lobjRecallData = New RecallData() With { _
                         .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex)), _
                         .OldInvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintOldInvestorIDIndex)), _
                         .NewFundID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintNewFundIDIndex)), _
                         .NewLinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintNewLinkIDIndex)), _
                         .OldLinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintOldLinkIDIndex)), _
                         .OldFundID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintOldFundIDIndex)), _
                         .FlagChangeTypeCode = DBParseUtils.GetValueString(reader.GetString(lintFlagChangeTypeCodeIndex)), _
                         .IsHandled = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintIsHandledIndex)) _
                        }
                        lstRecallData.Add(lobjRecallData)
                    End While
                    reader.NextResult()
                    PrepareInvestorFundDetails(reader, lstRecallAddResults, False)
                    reader.NextResult()
                    lintInvestorIDIndex = reader.GetOrdinal("InvestorID")
                    lintNewLinkIDIndex = reader.GetOrdinal("NewLinkID")
                    lintNewFundIDIndex = reader.GetOrdinal("NewFundID")
                    Dim lintListingIDIndex As Int16 = reader.GetOrdinal("ChangesInPPListingID")
                    Dim lintPositionIndex As Int16 = reader.GetOrdinal("Position")
                    Dim lintIsSumOfFundsIndex As Int16 = reader.GetOrdinal("IsSumOfFunds")
                    Dim lintFormulaIndex As Int16 = reader.GetOrdinal("Formula")
                    While reader.Read()
                        lobjPreviousProjectRecallData = New PreviousProjectRecallData() With { _
                         .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex)), _
                         .NewLinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintNewLinkIDIndex)), _
                         .NewFundID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintNewFundIDIndex)), _
                         .ListingID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintListingIDIndex)), _
                         .Positions = DBParseUtils.GetFieldValueInt64(reader.GetInt64(lintPositionIndex)), _
                         .IsSumOfFunds = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintIsSumOfFundsIndex)), _
                         .Formula = DBParseUtils.GetFieldValueString(reader.GetString(lintFormulaIndex)) _
                        }
                        lstPreviousProjectRecallData.Add(lobjPreviousProjectRecallData)
                    End While
                End Using
            End Using
        Finally
            lobjRecallData = Nothing
            'lstRecallData = Nothing
            lentDB = Nothing
        End Try
    End Sub

    Private Sub PrepareInvestorFundDetails(ByVal reader As IDataReader, ByRef lstRecallAddResults As IList(Of InvestorFundResults), ByVal pblnIsFromMaster As Boolean)
        Dim lintUserIDIndex As Int16
        Dim lintProjectIDIndex As Int16
        If pblnIsFromMaster Then
            lintUserIDIndex = reader.GetOrdinal("UserID")
            lintProjectIDIndex = reader.GetOrdinal("ProjectID")
        End If
        lstRecallAddResults = New List(Of InvestorFundResults)
        Dim lintIsMasterDBIndex As Int16 = reader.GetOrdinal("IsMasterDB")
        Dim lintRowNumberIndex As Int16 = reader.GetOrdinal("RowNumber")
        Dim lintDenseRankIndex As Int16 = reader.GetOrdinal("DenseRank")
        Dim lintFundDenseRankIndex As Int16 = reader.GetOrdinal("FundDenseRank")
        Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
        Dim lintLinkIDIndex As Int16 = reader.GetOrdinal("LinkID")
        Dim lintFundIDIndex As Int16 = reader.GetOrdinal("FundID")
        Dim lintInvestorNameIndex As Int16 = reader.GetOrdinal("InvestorName")
        Dim lintFundNameIndex As Int16 = reader.GetOrdinal("FundName")
        Dim lintIsInvestorIndex As Int16 = reader.GetOrdinal("IsInvestor")
        Dim lintIsManagedIndex As Int16 = reader.GetOrdinal("IsManaged")
        Dim lintIsCutRowIndex As Int16 = reader.GetOrdinal("IsCutRow")
        Dim lintNotesIndex As Int16 = reader.GetOrdinal("Notes")
        Dim lintOriginIndex As Int16 = reader.GetOrdinal("Origin")
        Dim lintIDIndex As Int16 = reader.GetOrdinal("ID")
        Dim lintInvStatusCodeIndex As Int16 = reader.GetOrdinal("InvStatusCode")
        Dim lintNameIndex As Int16 = reader.GetOrdinal("Name")
        Dim lintMInvestorTypeIDIndex As Int16 = reader.GetOrdinal("MInvestorTypeID")
        Dim lintHeadInvestorIndex As Int16 = reader.GetOrdinal("Head Investor")
        Dim lintParentIndex As Int16 = reader.GetOrdinal("Parent")
        Dim lintActivistIndex As Int16 = reader.GetOrdinal("Activist")
        Dim lintTypeIndex As Int16 = reader.GetOrdinal("Type")
        Dim lintStyleIndex As Int16 = reader.GetOrdinal("Style")
        Dim lintTurnoverIndex As Int16 = reader.GetOrdinal("Turnover")
        Dim lintCityIndex As Int16 = reader.GetOrdinal("City")
        Dim lintStateIndex As Int16 = reader.GetOrdinal("State")
        Dim lintCountryIndex As Int16 = reader.GetOrdinal("Country")
        Dim lintRegionIndex As Int16 = reader.GetOrdinal("Region")
        Dim lintFlagChangeTypeCodeIndex As Int16 = reader.GetOrdinal("FlagChangeTypeCode")
        Dim lobjRecallAddResults As InvestorFundResults = Nothing
        Try
            While reader.Read()
                lobjRecallAddResults = New InvestorFundResults() With { _
                 .IsMasterDB = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintIsMasterDBIndex)), _
                 .RowNumber = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintRowNumberIndex)), _
                 .DenseRank = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintDenseRankIndex)), _
                 .FundDenseRank = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundDenseRankIndex)), _
                 .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex)), _
                 .LinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintLinkIDIndex)), _
                 .FundID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundIDIndex)), _
                 .InvestorName = DBParseUtils.GetFieldValueString(reader.GetString(lintInvestorNameIndex)), _
                 .FundName = DBParseUtils.GetFieldValueString(reader.GetString(lintFundNameIndex)), _
                 .IsInvestor = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintIsInvestorIndex)), _
                 .IsManaged = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintIsManagedIndex)), _
                 .IsCutRow = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintIsCutRowIndex)), _
                 .Notes = DBParseUtils.GetFieldValueString(reader.GetString(lintNotesIndex)), _
                 .Origin = DBParseUtils.GetFieldValueString(reader.GetString(lintOriginIndex)), _
                 .ID = DBParseUtils.GetFieldValueString(reader.GetString(lintIDIndex)), _
                 .InvStatusCode = DBParseUtils.GetFieldValueString(reader.GetString(lintInvStatusCodeIndex)), _
                 .Name = DBParseUtils.GetFieldValueString(reader.GetString(lintNameIndex)), _
                 .MInvestorTypeID = DBParseUtils.GetFieldValueByte(reader.GetByte(lintMInvestorTypeIDIndex)), _
                 .HeadInvestor = DBParseUtils.GetFieldValueString(reader.GetString(lintHeadInvestorIndex)), _
                 .Parent = DBParseUtils.GetFieldValueString(reader.GetString(lintParentIndex)), _
                 .Activist = DBParseUtils.GetFieldValueString(reader.GetString(lintActivistIndex)), _
                 .Type = DBParseUtils.GetFieldValueString(reader.GetString(lintTypeIndex)), _
                 .Style = DBParseUtils.GetFieldValueString(reader.GetString(lintStyleIndex)), _
                 .Turnover = DBParseUtils.GetFieldValueString(reader.GetString(lintTurnoverIndex)), _
                 .City = DBParseUtils.GetFieldValueString(reader.GetString(lintCityIndex)), _
                 .State = DBParseUtils.GetFieldValueString(reader.GetString(lintStateIndex)), _
                 .Country = DBParseUtils.GetFieldValueString(reader.GetString(lintCountryIndex)), _
                 .Region = DBParseUtils.GetFieldValueString(reader.GetString(lintRegionIndex)), _
                 .FlagChangeTypeCode = DBParseUtils.GetFieldValueString(reader.GetString(lintFlagChangeTypeCodeIndex)) _
                }
                If pblnIsFromMaster Then
                    lobjRecallAddResults.UserID = DBParseUtils.GetFieldValueInt16(reader.GetInt16(lintUserIDIndex))
                    lobjRecallAddResults.ProjectID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintProjectIDIndex))
                End If
                lstRecallAddResults.Add(lobjRecallAddResults)
            End While
        Finally
            lobjRecallAddResults = Nothing
        End Try
    End Sub

    Public Function GetSnapshotFilePath(ByVal pintProjectID As Integer) As String
        Return DataFactory.CreateDataBase().ExecuteScalar("CR_sp_DH_GetSnapshotFilePath", New Object() {pintProjectID})
    End Function

    Public Sub GetPreviousSnapshotFilePath(ByVal pintProjectID As Integer, ByRef lstrSnapshotPath As String, ByRef lblnIsFromPreviousSnapshot As String)
        Dim reader As IDataReader = Nothing
        Try
            reader = DataFactory.CreateDataBase().ExecuteReader("CR_sp_DH_GetAnySnapshotFilePath", New Object() {pintProjectID})
            While reader.Read
                lstrSnapshotPath = reader.GetString(0)
                lblnIsFromPreviousSnapshot = reader.GetBoolean(1)
            End While
        Finally
            If reader IsNot Nothing Then
                If reader.IsClosed = False Then
                    reader.Close()
                    reader.Dispose()
                End If
            End If
        End Try
    End Sub

    Public Function GetEntityDoubtStatus(ByVal pintProjectID As Integer, ByVal pstrEntityIDs As String) As IList(Of EntityDoubtStatus)
        Dim lstData As IList(Of EntityDoubtStatus) = Nothing
        Dim lobjData As EntityDoubtStatus = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            lstData = New List(Of EntityDoubtStatus)
            Using cmd As DbCommand = lentDB.GetStoredProcCommand("CR_sp_DE_GetEntityDoubtStatus")
                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
                lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
                lentDB.AddInParameter(cmd, "EntityIDs", DbType.String, pstrEntityIDs)
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    Dim lintEntityIDIndex As Int16 = reader.GetOrdinal("EntityID")
                    Dim lintSourceNameIndex As Int16 = reader.GetOrdinal("SourceName")
                    Dim lintHasAnyDoubtsIndex As Int16 = reader.GetOrdinal("HasAnyDoubts")
                    While reader.Read()
                        lobjData = New EntityDoubtStatus() With { _
                         .EntityID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintEntityIDIndex)), _
                         .SourceName = DBParseUtils.GetValueString(reader.GetString(lintSourceNameIndex)), _
                         .HasDoubt = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintHasAnyDoubtsIndex)) _
                        }
                        lstData.Add(lobjData)
                    End While
                    Return lstData
                End Using
            End Using
        Finally
            lobjData = Nothing
            lstData = Nothing
            lentDB = Nothing
        End Try
    End Function

    Public Sub InsertUpdateSnapshot(ByVal pintProjectID As Integer, ByVal pstrSnapshotPath As String, ByVal pblnIsFromDE As Boolean, ByVal pstrHiddenColumns As String)
        DataFactory.CreateDataBase().ExecuteNonQuery("CR_sp_DH_ManageSnapshot", New Object() {pintProjectID, pstrSnapshotPath, CommonCLL.UserID, pblnIsFromDE, pstrHiddenColumns})
    End Sub

    Public Function GetLockedUpdatedData(ByVal pintProjectID As Integer, ByVal pstrLockingEntityIDs As String, _
                                         ByVal lstNonPivotCols As List(Of String), ByVal lstPivotCols As List(Of String), _
                                         ByVal pstrPreviousProjectName As String, ByVal lstActiveDataSources As List(Of CurrentDataSources), _
                                         ByVal pintPreviousProjectID As Integer, ByRef pdtInvFundData As DataTable) As DataTable
        Dim ldtPivotedData As DataTable = Nothing
        Dim ldrData As IDataReader = Nothing
        'Dim keyColumn(1) As DataColumn
        Try
            ldrData = DataFactory.CreateDataBase().ExecuteReader("CR_sp_DE_GetDataForLockedEntity", {pintProjectID, CommonCLL.UserID, pstrLockingEntityIDs, pintPreviousProjectID, pstrPreviousProjectName})
            'pdtInvFundData = New DataTable
            'pdtInvFundData.Load(ldrData, LoadOption.Upsert)
            'keyColumn(0) = pdtInvFundData.Columns("InvestorID")
            'keyColumn(1) = pdtInvFundData.Columns("FundID")
            'pdtInvFundData.PrimaryKey = keyColumn
            ldtPivotedData = JeffPivot(ldrData, "", "SourceName", lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
            Return ldtPivotedData
        Finally
            If ldrData.IsClosed = False Then
                ldrData.Close()
            End If
            ldrData = Nothing
            ldtPivotedData = Nothing
        End Try
    End Function

    Public Function GetUpdatedDataForSOFFormula(ByVal pintProjectID As Integer, ByVal pstrLockingEntityIDs As String, _
                                         ByVal lstNonPivotCols As List(Of String), ByVal lstPivotCols As List(Of String), _
                                         ByVal pstrPreviousProjectName As String) As DataTable
        Dim ldtPivotedData As DataTable = Nothing
        Dim ldrData As IDataReader = Nothing
        Try
            ldrData = DataFactory.CreateDataBase().ExecuteReader("CR_sp_DE_GetDataForSourceSOFFormula", {pintProjectID, pstrLockingEntityIDs})
            ldtPivotedData = JeffPivot(ldrData, "", "SourceName", lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
            Return ldtPivotedData
        Finally
            ldrData.Close()
            ldrData = Nothing
            ldtPivotedData = Nothing
        End Try
    End Function

    Public Sub GetSourceSOFStatus(ByVal pintProjectID As Integer, ByVal pstrInvestorIDs As String, _
                                         ByVal lstNonPivotCols As List(Of String), ByVal lstPivotCols As List(Of String), _
                                         ByVal pstrPreviousProjectName As String, ByRef ldtSourceSOFStatus As DataTable, ByRef ldtFLSOFStatus As DataTable, ByVal pintClientID As Integer)
        Dim ldrData As IDataReader = Nothing
        Try
            ldrData = DataFactory.CreateDataBase().ExecuteReader("CR_sp_DE_GetIndividualSOFStatus", {pintProjectID, pstrInvestorIDs, pintClientID})
            ldtSourceSOFStatus = JeffPivot(ldrData, "", "Position", lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
            ldrData.NextResult()
            ldtFLSOFStatus = JeffPivot(ldrData, "", "Position", lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
        Finally
            ldrData.Close()
            ldrData = Nothing
        End Try
    End Sub

    Public Function GetPreviousListingData(ByVal pintProjectID As Integer, ByVal pstrRetrievingEntityIDs As String, _
                                         ByVal lstNonPivotCols As List(Of String), ByVal lstPivotCols As List(Of String), ByVal pstrPreviousProjectName As String) As DataTable
        Dim ldtPivotedData As DataTable = Nothing
        Dim ldrData As IDataReader = Nothing
        Try
            ldrData = DataFactory.CreateDataBase().ExecuteReader("CR_sp_DE_GetDataForPreviousListing", {pintProjectID, pstrRetrievingEntityIDs})
            ldtPivotedData = JeffPivot(ldrData, "", "ListingID", lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
            Return ldtPivotedData
        Finally
            ldrData.Close()
            ldrData = Nothing
            ldtPivotedData = Nothing
        End Try
    End Function

    Public Sub GetSOFFormulaInListingForRecord(ByVal pintInvestorID As Integer, ByVal pintLinkID As Integer, ByVal pintListingID As Integer, ByVal pintEntityID As Integer, _
                                                    ByRef pstrFormula As String, ByRef pblnSOF As Boolean)
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            Using cmd As DbCommand = lentDB.GetStoredProcCommand("CR_sp_FL_GetSOFFormulaInListingForRecord")
                lentDB.AddInParameter(cmd, "InvestorID", DbType.Int32, pintInvestorID)
                lentDB.AddInParameter(cmd, "LinkID", DbType.Int32, pintLinkID)
                lentDB.AddInParameter(cmd, "ListingID", DbType.Int32, pintListingID)
                lentDB.AddInParameter(cmd, "EntityID", DbType.Int32, pintEntityID)
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    While reader.Read()
                        pstrFormula = DBParseUtils.GetValueString(reader.GetString(0))
                        pblnSOF = DBParseUtils.GetFieldValueBool(reader.GetBoolean(1))
                    End While
                End Using
            End Using
        Finally
            lentDB = Nothing
        End Try
    End Sub

    Public Sub AccessDataSource(ByVal pstrDataEntities As String, ByVal pblnIsLocked As Boolean, ByRef penmResult As DBResult, ByRef pstrErrorMessage As String, ByRef pstrUserFullName As String, ByRef pintLastUserID As Integer)
        'Return DataFactory.CreateDataBase().ExecuteReader("CR_sp_EDS_ImportESource", New Object() {pintEntityID, pintListingID, pintProjectID, pintETemplateID, pintClientID, penmSourceTypeCode})
        Dim cmd As New SqlCommand
        Dim sqlParam(6) As SqlParameter
        'Dim ldtResults As New DataTable
        'Dim ldsResults As DataSet = Nothing
        cmd.CommandTimeout = 0
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "CR_sp_DH_ManageSourceEntityAccess"

        sqlParam(0) = New SqlParameter("@DataEntities", pstrDataEntities)
        cmd.Parameters.Add(sqlParam(0))
        sqlParam(1) = New SqlParameter("@IsLocked", pblnIsLocked)
        cmd.Parameters.Add(sqlParam(1))
        sqlParam(2) = New SqlParameter("@CurrentUserID", CommonCLL.UserID)
        cmd.Parameters.Add(sqlParam(2))
        sqlParam(3) = New SqlParameter("@Result", SqlDbType.Int)
        sqlParam(3).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(3))
        sqlParam(4) = New SqlParameter("@ErrorMessage", SqlDbType.VarChar)
        sqlParam(4).Size = 2000
        sqlParam(4).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(4))
        sqlParam(5) = New SqlParameter("@UserFullName", SqlDbType.VarChar)
        sqlParam(5).Size = 100
        sqlParam(5).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(5))
        sqlParam(6) = New SqlParameter("@LastUserID", SqlDbType.Int)
        sqlParam(6).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(6))

        CreateDataBase.ExecuteNonQuery(cmd)
        'ldsResults = New DataSet
        'ldsResults = CreateDataBase.ExecuteDataSet(cmd)
        'If ldsResults.Tables.Count > 0 Then
        '    ldtResults = ldsResults.Tables(0)
        'End If
        If (cmd.Parameters(3).Value IsNot DBNull.Value) Then
            penmResult = CInt(cmd.Parameters(3).Value)
        End If
        If (cmd.Parameters(4).Value IsNot DBNull.Value) Then
            pstrErrorMessage = cmd.Parameters(4).Value.ToString
        End If
        If (cmd.Parameters(5).Value IsNot DBNull.Value) Then
            pstrUserFullName = cmd.Parameters(5).Value.ToString
        End If
        If (cmd.Parameters(6).Value IsNot DBNull.Value) Then
            pintLastUserID = cmd.Parameters(6).Value.ToString
        End If
        'pintLastUserID
        'Return ldtResults
    End Sub

    Public Function GetAccessDataSourceStatus(ByVal pintEntityID As Integer, ByVal pintUserID As Integer) As IList(Of SourceEntityAccessStatus)
        Dim lstData As IList(Of SourceEntityAccessStatus) = Nothing
        Dim lobjData As SourceEntityAccessStatus = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            lstData = New List(Of SourceEntityAccessStatus)
            Using cmd As DbCommand = lentDB.GetStoredProcCommand("CR_sp_DH_GetSourceEntityAccessStatus")
                lentDB.AddInParameter(cmd, "EntityID", DbType.Int32, pintEntityID)
                lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    Dim lintUserIDIndex As Int16 = reader.GetOrdinal("UserID")
                    Dim lintUserFullNameIndex As Int16 = reader.GetOrdinal("UserFullName")
                    While reader.Read()
                        lobjData = New SourceEntityAccessStatus() With { _
                         .UserID = DBParseUtils.GetFieldValueInt16(reader.GetInt16(lintUserIDIndex)), _
                         .UserFullName = DBParseUtils.GetValueString(reader.GetString(lintUserFullNameIndex)) _
                        }
                        lstData.Add(lobjData)
                    End While
                    Return lstData
                End Using
            End Using
        Finally
            lobjData = Nothing
            lstData = Nothing
            lentDB = Nothing
        End Try
    End Function

    Public Function GetDEScreenStatus(ByVal pintProjectID As Integer) As Boolean
        Return DataFactory.CreateDataBase().ExecuteScalar("CR_sp_PRJ_GetDEScreenStatus", {pintProjectID})
    End Function

    Public Function GetNotes(ByVal pintDEProjectID As Integer, ByVal pintFLProjectID As Integer, ByVal pintInvestorID As Integer, ByVal pintLinkID As Integer) As IList(Of Notes)
        Dim lstData As IList(Of Notes) = Nothing
        Dim lobjData As Notes = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            lstData = New List(Of Notes)
            Using cmd As DbCommand = lentDB.GetStoredProcCommand("CR_sp_DH_GetNotes")
                lentDB.AddInParameter(cmd, "DEProjectID", DbType.Int32, pintDEProjectID)
                lentDB.AddInParameter(cmd, "FLProjectID", DbType.Int32, pintFLProjectID)
                lentDB.AddInParameter(cmd, "InvestorID", DbType.Int32, pintInvestorID)
                lentDB.AddInParameter(cmd, "LinkID", DbType.Int32, pintLinkID)
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    Dim lintDENotesIndex As Int16 = reader.GetOrdinal("DENotes")
                    Dim lintFLNotesIndex As Int16 = reader.GetOrdinal("FLNotes")
                    Dim lintNotesIndex As Int16 = reader.GetOrdinal("Notes")
                    While reader.Read()
                        lobjData = New Notes() With { _
                         .DENotes = DBParseUtils.GetValueString(reader.GetString(lintDENotesIndex)), _
                         .FLNotes = DBParseUtils.GetValueString(reader.GetString(lintFLNotesIndex)), _
                         .Notes = DBParseUtils.GetValueString(reader.GetString(lintNotesIndex)) _
                        }
                        lstData.Add(lobjData)
                    End While
                    Return lstData
                End Using
            End Using
        Finally
            lobjData = Nothing
            lstData = Nothing
            lentDB = Nothing
        End Try
    End Function

    Public Function ManageNotes(ByVal pintProjectID As Integer, ByVal pstrInvestorLinkIDs As String, ByVal pstrDENotes As String, ByVal pstrFLNotes As String, _
                           ByVal pintUserID As Int16, ByRef pintResult As Integer, ByVal penmDENotesSaveType As NotesSaveType, _
                           ByVal penmFLNotesSaveType As NotesSaveType, ByVal pintCurrentInvestorID As Integer, ByVal pintCurrentLinkID As Integer, ByRef pstrNotes As String) As IList(Of NotesStatus)
        Dim lstData As New List(Of NotesStatus)
        Dim lobjData As NotesStatus = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_DH_ManageNotes")
                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
                lentDB.AddInParameter(cmd, "DENotes", DbType.String, pstrDENotes)
                lentDB.AddInParameter(cmd, "FLNotes", DbType.String, pstrFLNotes)
                lentDB.AddInParameter(cmd, "InvestorLinkIDs", DbType.String, pstrInvestorLinkIDs)
                lentDB.AddInParameter(cmd, "UserID", DbType.Int32, pintUserID)
                lentDB.AddInParameter(cmd, "DENotesSaveType", DbType.Int16, penmDENotesSaveType)
                lentDB.AddInParameter(cmd, "FLNotesSaveType", DbType.Int16, penmFLNotesSaveType)
                cmd.CommandTimeout = 0
                Using reader As IDataReader = DataFactory.CreateDataBase().ExecuteReader(cmd)
                    Dim lintNotesIDIndex As Int16 = reader.GetOrdinal("Notes")
                    Dim lintFundIDIndex As Int16 = reader.GetOrdinal("FundID")
                    Dim lintLinkIDIndex As Int16 = reader.GetOrdinal("LinkID")
                    Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
                    cmd.CommandTimeout = 0
                    While reader.Read()
                        lobjData = New NotesStatus() With { _
                         .Notes = DBParseUtils.GetFieldValueString(reader.GetString(lintNotesIDIndex)), _
                         .FundID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundIDIndex)), _
                         .LinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintLinkIDIndex)), _
                         .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex)) _
                        }
                        lstData.Add(lobjData)
                    End While
                    reader.NextResult()
                    While reader.Read()
                        pintResult = reader.GetInt32(reader.GetOrdinal("Result"))
                    End While
                    Return lstData
                End Using
            End Using
        Finally
            lobjData = Nothing
            lstData = Nothing
        End Try
    End Function

    Public Sub DeleteRemoveInvFund(ByVal pintProjectID As Integer, ByVal pstrDataToDeleteInvIDs As String, ByVal pstrReferencedDataInvIDs As String, ByVal pstrDataToDeleteLinkIDs As String, ByVal pstrReferencedDataLinkIDs As String, ByVal pblnIsDelete As Boolean, ByRef penmResult As DBResult, ByRef pstrErrorMessage As String)
        'Return DataFactory.CreateDataBase().ExecuteReader("CR_sp_EDS_ImportESource", New Object() {pintEntityID, pintListingID, pintProjectID, pintETemplateID, pintClientID, penmSourceTypeCode})
        Dim cmd As New SqlCommand
        Dim sqlParam(8) As SqlParameter
        cmd.CommandTimeout = 0
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "CR_sp_DH_DeleteRemoveInvFund"

        sqlParam(0) = New SqlParameter("@ProjectID", pintProjectID)
        cmd.Parameters.Add(sqlParam(0))
        sqlParam(1) = New SqlParameter("@DataToDeleteInvIDs", pstrDataToDeleteInvIDs)
        cmd.Parameters.Add(sqlParam(1))
        sqlParam(2) = New SqlParameter("@ReferencedDataInvIDs", pstrReferencedDataInvIDs)
        cmd.Parameters.Add(sqlParam(2))
        sqlParam(3) = New SqlParameter("@DataToDeleteLinkIDs", pstrDataToDeleteLinkIDs)
        cmd.Parameters.Add(sqlParam(3))
        sqlParam(4) = New SqlParameter("@ReferencedDataLinkIDs", pstrReferencedDataLinkIDs)
        cmd.Parameters.Add(sqlParam(4))
        sqlParam(5) = New SqlParameter("@IsDelete", pblnIsDelete)
        cmd.Parameters.Add(sqlParam(5))
        sqlParam(6) = New SqlParameter("@Result", SqlDbType.Int)
        sqlParam(6).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(6))
        sqlParam(7) = New SqlParameter("@ErrorMessage", SqlDbType.VarChar)
        sqlParam(7).Size = 2000
        sqlParam(7).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(7))
        sqlParam(8) = New SqlParameter("@UserID", CommonCLL.UserID)
        cmd.Parameters.Add(sqlParam(8))

        CreateDataBase.ExecuteNonQuery(cmd)
        If (cmd.Parameters(6).Value IsNot DBNull.Value) Then
            penmResult = CInt(cmd.Parameters(6).Value)
        End If
        If (cmd.Parameters(7).Value IsNot DBNull.Value) Then
            pstrErrorMessage = cmd.Parameters(7).Value.ToString
        End If
    End Sub

    Public Sub GetDeleteRemoveInvFundStatus(ByVal pintProjectID As Integer, ByVal pstrInvestorDataToDeleteIDs As String, ByVal pstrFundDataToDeleteIDs As String, _
                                                 ByRef lstDataSourceInvStatus As IList(Of LinkingDetails), ByRef lstDataSourceFundStatus As IList(Of LinkingDetails))
        'Return DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_DH_GetDeleteRemoveInvFundStatus", {pintProjectID, pstrInvestorDataToDeleteIDs, pstrFundDataToDeleteIDs, pblnIsInvestor})
        Dim lobjData As LinkingDetails = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            Using cmd As DbCommand = lentDB.GetStoredProcCommand("CR_sp_DH_GetDeleteRemoveInvFundStatus")
                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
                lentDB.AddInParameter(cmd, "DataToDeleteInvIDs", DbType.String, pstrInvestorDataToDeleteIDs)
                lentDB.AddInParameter(cmd, "DataToDeleteLinkIDs", DbType.String, pstrFundDataToDeleteIDs)
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
                    While reader.Read()
                        lobjData = New LinkingDetails() With { _
                         .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex))
                        }
                        lstDataSourceInvStatus.Add(lobjData)
                    End While

                    reader.NextResult()
                    Dim lintLinkIDIndex As Int16 = reader.GetOrdinal("LinkID")
                    lintInvestorIDIndex = reader.GetOrdinal("InvestorID")
                    While reader.Read()
                        lobjData = New LinkingDetails() With { _
                         .LinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintLinkIDIndex)), _
                        .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex))
                        }
                        lstDataSourceFundStatus.Add(lobjData)
                    End While
                End Using
            End Using
        Finally
            lobjData = Nothing
            lentDB = Nothing
            'reader = Nothing
        End Try
    End Sub

    Public Function GetDataEntityAccessHistory(ByVal pintEntityID As Integer, ByVal pintListingID As Integer, ByVal pblnIsForListing As Boolean) As String
        Return DataFactory.CreateDataBase().ExecuteScalar("CR_sp_DH_GetDataEntityAccessHistory", {pintEntityID, pintListingID, pblnIsForListing})
    End Function

    Public Function GetEntityLockStatus(ByVal pintProjectID As Integer) As Boolean
        Return DataFactory.CreateDataBase().ExecuteScalar("CR_sp_DH_GetEntityLockStatus", {pintProjectID, CommonCLL.UserID})
    End Function

    Public Function GetLinking(ByVal pstrLinkingInformation As String) As IList(Of LinkingDetails)
        'Return DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_DH_GetLinking", {pstrLinkingInformation}).Tables(0)
        Dim lstData As New List(Of LinkingDetails)
        Dim lobjData As LinkingDetails = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_DH_GetLinking")
                lentDB.AddInParameter(cmd, "LinkingInfo", DbType.String, pstrLinkingInformation)
                cmd.CommandTimeout = 0
                Using reader As IDataReader = DataFactory.CreateDataBase().ExecuteReader(cmd)
                    Dim lintLinkIDIndex As Int16 = reader.GetOrdinal("LinkID")
                    Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
                    Dim lintFundIDIndex As Int16 = reader.GetOrdinal("FundID")
                    While reader.Read()
                        lobjData = New LinkingDetails() With { _
                         .LinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintLinkIDIndex)), _
                         .InvestorID = DBParseUtils.GetValueString(reader.GetInt32(lintInvestorIDIndex)), _
                         .FundID = DBParseUtils.GetValueString(reader.GetInt32(lintFundIDIndex)) _
                        }
                        lstData.Add(lobjData)
                    End While
                    Return lstData
                End Using
            End Using
        Finally
            lobjData = Nothing
            lstData = Nothing
        End Try
    End Function

    Public Sub DeleteDataEntity(ByVal pstrEntityIDs As String, ByRef penmResult As DBResult, ByRef pstrErrorMessage As String)
        Dim cmd As New SqlCommand
        Dim sqlParam(2) As SqlParameter
        cmd.CommandTimeout = 0
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "CR_sp_DH_DeleteDataEntity"

        sqlParam(0) = New SqlParameter("@EntityIDs", pstrEntityIDs)
        cmd.Parameters.Add(sqlParam(0))
        sqlParam(1) = New SqlParameter("@Result", SqlDbType.Int)
        sqlParam(1).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(1))
        sqlParam(2) = New SqlParameter("@ErrorMessage", SqlDbType.VarChar)
        sqlParam(2).Size = 2000
        sqlParam(2).Direction = ParameterDirection.Output
        cmd.Parameters.Add(sqlParam(2))
        CreateDataBase.ExecuteNonQuery(cmd)
        If (cmd.Parameters(1).Value IsNot DBNull.Value) Then
            penmResult = CInt(cmd.Parameters(1).Value)
        End If
        If (cmd.Parameters(2).Value IsNot DBNull.Value) Then
            pstrErrorMessage = cmd.Parameters(2).Value.ToString
        End If
    End Sub

    Public Function GetSortedData(ByVal pintProjectID As Integer, ByVal pintSortingOn As Int16) As DataTable
        Return DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_DH_GetSortedData", {pintProjectID, CommonCLL.UserID, pintSortingOn}).Tables(0)
        'Dim lstData As New List(Of UpdatedSortData)
        'Dim lobjData As UpdatedSortData = Nothing
        'Dim lentDB As Database = DataFactory.CreateDataBase()
        'Try
        '    Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_DH_GetSortedData")
        '        lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
        '        lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
        '        lentDB.AddInParameter(cmd, "SortingOn", DbType.Int16, pintSortingOn)
        '        cmd.CommandTimeout = 0
        '        Using reader As IDataReader = lentDB.ExecuteReader(cmd)
        '            Dim lintDenseRankIndex As Int16 = reader.GetOrdinal("DenseRank")
        '            Dim lintFundDenseRankIndex As Int16 = reader.GetOrdinal("FundDenseRank")
        '            While reader.Read()
        '                lobjData = New UpdatedSortData() With { _
        '                 .DenseRank = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintDenseRankIndex)), _
        '                 .FundDenseRank = DBParseUtils.GetValueString(reader.GetInt32(lintFundDenseRankIndex)) _
        '                }
        '                lstData.Add(lobjData)
        '            End While
        '            reader.Dispose()
        '            Return lstData
        '        End Using
        '    End Using
        'Finally
        '    lobjData = Nothing
        '    lstData = Nothing
        'End Try
    End Function

    'Public Function GetSortedData(ByVal pintProjectID As Integer, ByVal pintSortingOn As Int16) As IList(Of UpdatedSortData)
    '    'Return DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_DH_GetSortedData", {pintProjectID, CommonCLL.UserID, pintSortingOn}).Tables(0)
    '    Dim lstData As New List(Of UpdatedSortData)
    '    Dim lobjData As UpdatedSortData = Nothing
    '    Dim lentDB As Database = DataFactory.CreateDataBase()
    '    Try
    '        Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_DH_GetSortedData")
    '            lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
    '            lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
    '            lentDB.AddInParameter(cmd, "SortingOn", DbType.Int16, pintSortingOn)
    '            cmd.CommandTimeout = 0
    '            Using reader As IDataReader = lentDB.ExecuteReader(cmd)
    '                Dim lintDenseRankIndex As Int16 = reader.GetOrdinal("DenseRank")
    '                Dim lintFundDenseRankIndex As Int16 = reader.GetOrdinal("FundDenseRank")
    '                While reader.Read()
    '                    lobjData = New UpdatedSortData() With { _
    '                     .DenseRank = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintDenseRankIndex)), _
    '                     .FundDenseRank = DBParseUtils.GetValueString(reader.GetInt32(lintFundDenseRankIndex)) _
    '                    }
    '                    lstData.Add(lobjData)
    '                End While
    '                reader.Dispose()
    '                Return lstData
    '            End Using
    '        End Using
    '    Finally
    '        lobjData = Nothing
    '        lstData = Nothing
    '    End Try
    'End Function

    Public Function CheckValidMandACode(ByVal pstrMandACode As String) As Boolean
        Return DataFactory.CreateDataBase().ExecuteScalar("CR_sp_DE_CheckValidMandACode", {pstrMandACode})
    End Function

    Public Function GetMasterData(ByVal pintProjectID As Integer, ByVal pstrInvSearchText1 As String, ByVal pstrInvSearchText2 As String, _
                                  ByVal pblnIsInvContains As Boolean, ByVal pblnIsInvAND As Boolean, ByVal pstrFundSearchText1 As String, _
                                  ByVal pstrFundSearchText2 As String, ByVal pblnIsFundContains As Boolean, ByVal pblnIsFundAND As Boolean, _
                                  ByVal pintSearchType As Int16) As IList(Of InvestorFundResults)
        'Return DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_DE_GetMasterData", {pintProjectID, CommonCLL.UserID, pstrInvSearchText1, pstrInvSearchText2, _
        '                                                                              pblnIsInvContains, pblnIsInvAND, pstrFundSearchText1, pstrFundSearchText2, _
        '                                                                              pblnIsFundContains, pblnIsFundAND, pintSearchType}).Tables(0)

        Dim lstData As New List(Of InvestorFundResults)
        Dim lobjData As InvestorFundResults = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Dim reader As IDataReader = Nothing
        Dim lstrError As String
        Try
            Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_DE_GetMasterData")
                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
                lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
                lentDB.AddInParameter(cmd, "InvSearchText1", DbType.String, pstrInvSearchText1)
                lentDB.AddInParameter(cmd, "InvSearchText2", DbType.String, pstrInvSearchText2)
                lentDB.AddInParameter(cmd, "IsInvContains", DbType.Boolean, pblnIsInvContains)
                lentDB.AddInParameter(cmd, "IsInvAND", DbType.Boolean, pblnIsInvAND)
                lentDB.AddInParameter(cmd, "FundSearchText1", DbType.String, pstrFundSearchText1)
                lentDB.AddInParameter(cmd, "FundSearchText2", DbType.String, pstrFundSearchText2)
                lentDB.AddInParameter(cmd, "IsFundContains", DbType.Boolean, pblnIsFundContains)
                lentDB.AddInParameter(cmd, "IsFundAND", DbType.Boolean, pblnIsFundAND)
                lentDB.AddInParameter(cmd, "SearchType", DbType.Int16, pintSearchType)
                cmd.CommandTimeout = 0
                reader = lentDB.ExecuteReader(cmd)
                PrepareInvestorFundDetails(reader, lstData, True)
                Return lstData
            End Using
        Catch ex1 As System.Threading.ThreadAbortException
        Catch ex As Exception
            lstrError = ex.ToString
        Finally
            lobjData = Nothing
            lstData = Nothing
            If reader IsNot Nothing Then
                If reader.IsClosed = False Then
                    reader.Close()
                    reader.Dispose()
                End If
            End If
        End Try
    End Function

    Public Sub GetMAData(ByVal pintProjectID As Integer, ByVal pstrMASearchCode As String, ByRef lstMasterData As IList(Of InvestorFundResults), ByRef lstMAInvestors As IList(Of MAInvestors))
        'Return DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_DE_GetMAData", {pintProjectID, CommonCLL.UserID, pstrMASearchCode})
        'Dim lstData As New List(Of UpdatedSortData)
        Dim lobjMasterData As InvestorFundResults = Nothing
        Dim lobjMAInvestor As MAInvestors = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            lstMAInvestors = New List(Of MAInvestors)
            Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_DE_GetMAData")
                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
                lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
                lentDB.AddInParameter(cmd, "MASearchCode", DbType.String, pstrMASearchCode)
                cmd.CommandTimeout = 0
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    PrepareInvestorFundDetails(reader, lstMasterData, True)
                    reader.NextResult()
                    Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
                    Dim lintInvestorNameIndex As Int16 = reader.GetOrdinal("InvestorName")
                    Dim lintParentInvestorIDIndex As Int16 = reader.GetOrdinal("ParentInvestorID")
                    While reader.Read()
                        lobjMAInvestor = New MAInvestors() With { _
                         .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex)), _
                         .InvestorName = DBParseUtils.GetValueString(reader.GetString(lintInvestorNameIndex)), _
                         .ParentInvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintParentInvestorIDIndex)) _
                        }
                        lstMAInvestors.Add(lobjMAInvestor)
                    End While
                    reader.Dispose()
                End Using
            End Using
        Finally
            lobjMasterData = Nothing
            lobjMAInvestor = Nothing
        End Try
    End Sub

    Public Sub GetChangeLogData(ByVal pintProjectID As Integer, ByVal pstrChangeLogSearchCode As String, ByRef lstMasterData As IList(Of InvestorFundResults), ByRef lstChangeLogData As IList(Of LinkingDetails))
        'Return DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_DE_GetESourceChangeLogData", {pintProjectID, CommonCLL.UserID, pstrChangeLogSearchCode})
        Dim lobjMasterData As InvestorFundResults = Nothing
        Dim lobjMAInvestor As LinkingDetails = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_DE_GetESourceChangeLogData")
                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
                lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
                lentDB.AddInParameter(cmd, "ChangeLogSearchCode", DbType.String, pstrChangeLogSearchCode)
                cmd.CommandTimeout = 0
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    PrepareInvestorFundDetails(reader, lstMasterData, True)
                    reader.NextResult()
                    Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
                    Dim lintInvestorNameIndex As Int16 = reader.GetOrdinal("InvestorName")
                    Dim lintFundIDIndex As Int16 = reader.GetOrdinal("FundID")
                    Dim lintFundNameIndex As Int16 = reader.GetOrdinal("FundName")
                    Dim lintLinkIDIndex As Int16 = reader.GetOrdinal("LinkID")
                    lstChangeLogData = New List(Of LinkingDetails)
                    While reader.Read()
                        lobjMAInvestor = New LinkingDetails() With { _
                         .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex)), _
                         .InvestorName = DBParseUtils.GetValueString(reader.GetString(lintInvestorNameIndex)), _
                         .FundID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundIDIndex)), _
                         .FundName = DBParseUtils.GetValueString(reader.GetString(lintFundNameIndex)), _
                         .LinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintLinkIDIndex)) _
                        }
                        lstChangeLogData.Add(lobjMAInvestor)
                    End While
                End Using

            End Using
        Finally
            lobjMasterData = Nothing
            lobjMAInvestor = Nothing
        End Try
    End Sub

    Public Function GetLinkedFunds(ByVal pintInvestorID As Integer, ByVal pintProjectID As Integer, ByVal pintUserID As Integer) As List(Of AddNewInvestor)
        Dim lstData As New List(Of AddNewInvestor)
        Dim lobjData As AddNewInvestor = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_DE_GetLinkedFunds")
                lentDB.AddInParameter(cmd, "InvestorID", DbType.Int32, pintInvestorID)
                lentDB.AddInParameter(cmd, "UserID", DbType.Int32, pintUserID)
                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int16, pintProjectID)
                cmd.CommandTimeout = 0
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    Dim lintUserIDIndex As Int16 = reader.GetOrdinal("UserID")
                    Dim lintProjectIDIndex As Int16 = reader.GetOrdinal("ProjectID")
                    Dim lintRowNumberIndex As Int16 = reader.GetOrdinal("RowNumber")
                    Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
                    Dim lintILinkIDIndex As Int16 = reader.GetOrdinal("LinkID")
                    Dim lintFundIDIndex As Int16 = reader.GetOrdinal("FundID")
                    Dim lstrInvestorName As Int16 = reader.GetOrdinal("InvestorName")
                    Dim lstrFundName As Int16 = reader.GetOrdinal("FundName")
                    Dim lblnIsInvestor As Int16 = reader.GetOrdinal("IsInvestor")
                    Dim lblnIsManaged As Int16 = reader.GetOrdinal("IsManaged")
                    Dim lblnIsCutRow As Int16 = reader.GetOrdinal("IsCutRow")
                    Dim lstrNotes As Int16 = reader.GetOrdinal("Notes")
                    Dim lstrOrigin As Int16 = reader.GetOrdinal("Origin")
                    Dim lstrID As Int16 = reader.GetOrdinal("ID")
                    Dim lstrInvStatusCode As Int16 = reader.GetOrdinal("InvStatusCode")
                    Dim lstrName As Int16 = reader.GetOrdinal("Name")
                    Dim lintMInvestorTypeID As Int16 = reader.GetOrdinal("MInvestorTypeID")
                    Dim lstrHeadInvestor As Int16 = reader.GetOrdinal("Head Investor")
                    Dim lstrParent As Int16 = reader.GetOrdinal("Parent")
                    Dim lstrActivist As Int16 = reader.GetOrdinal("Activist")
                    Dim lstrType As Int16 = reader.GetOrdinal("Type")
                    Dim lstrStyle As Int16 = reader.GetOrdinal("Style")
                    Dim lstrTurnover As Int16 = reader.GetOrdinal("Turnover")
                    Dim lstrCity As Int16 = reader.GetOrdinal("City")
                    Dim lstrState As Int16 = reader.GetOrdinal("State")
                    Dim lstrCountry As Int16 = reader.GetOrdinal("Country")
                    Dim lstrRegion As Int16 = reader.GetOrdinal("Region")
                    Dim lstrStatus As Int16 = reader.GetOrdinal("Status")
                    Dim lintDenseRankIndex As Int16 = reader.GetOrdinal("DenseRank")
                    Dim lintFundDenseRankIndex As Int16 = reader.GetOrdinal("FundDenseRank")
                    Dim lblnIsMasterDB As Int16 = reader.GetOrdinal("IsMasterDB")
                    While reader.Read()
                        lobjData = New AddNewInvestor() With { _
                         .UserId = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintUserIDIndex)), _
                         .ProjectId = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintProjectIDIndex)), _
                         .RowNumber = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintRowNumberIndex)), _
                         .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex)), _
                         .LinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintILinkIDIndex)), _
                         .FundID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundIDIndex)), _
                         .InvestorName = DBParseUtils.GetFieldValueString(reader.GetString(lstrInvestorName)), _
                         .FundName = DBParseUtils.GetFieldValueString(reader.GetString(lstrFundName)), _
                         .IsInvestor = DBParseUtils.GetFieldValueString(reader.GetBoolean(lblnIsInvestor)), _
                         .IsManaged = DBParseUtils.GetFieldValueString(reader.GetBoolean(lblnIsManaged)), _
                         .IsCutRow = DBParseUtils.GetFieldValueString(reader.GetBoolean(lblnIsCutRow)), _
                         .Notes = DBParseUtils.GetFieldValueString(reader.GetString(lstrNotes)), _
                         .Origin = DBParseUtils.GetFieldValueString(reader.GetString(lstrOrigin)), _
                         .ID = DBParseUtils.GetFieldValueString(reader.GetString(lstrID)), _
                         .InvStatusCode = DBParseUtils.GetFieldValueString(reader.GetString(lstrInvStatusCode)), _
                         .Name = DBParseUtils.GetFieldValueString(reader.GetString(lstrName)), _
                         .MInvestorTypeID = DBParseUtils.GetFieldValueInt32(reader.GetByte(lintMInvestorTypeID)), _
                         .HeadInvestor = DBParseUtils.GetFieldValueString(reader.GetString(lstrHeadInvestor)), _
                         .Parent = DBParseUtils.GetFieldValueString(reader.GetString(lstrParent)), _
                         .Activist = DBParseUtils.GetFieldValueString(reader.GetString(lstrActivist)), _
                         .Type = DBParseUtils.GetFieldValueString(reader.GetString(lstrType)), _
                         .Style = DBParseUtils.GetFieldValueString(reader.GetString(lstrStyle)), _
                         .Turnover = DBParseUtils.GetFieldValueString(reader.GetString(lstrTurnover)), _
                         .City = DBParseUtils.GetFieldValueString(reader.GetString(lstrCity)), _
                         .State = DBParseUtils.GetFieldValueString(reader.GetString(lstrState)), _
                         .Country = DBParseUtils.GetFieldValueString(reader.GetString(lstrCountry)), _
                         .Region = DBParseUtils.GetFieldValueString(reader.GetString(lstrRegion)), _
                         .Status = DBParseUtils.GetFieldValueString(reader.GetString(lstrStatus)), _
                         .DenseRank = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintDenseRankIndex)), _
                         .FundDenseRank = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundDenseRankIndex)), _
                         .IsMasterDB = DBParseUtils.GetFieldValueString(reader.GetBoolean(lblnIsMasterDB)) _
                        }
                        lstData.Add(lobjData)
                    End While
                End Using
            End Using
            Return lstData
        Finally
        End Try
    End Function

    Public Function GetInvestorsOfFund(ByVal pintFundID As Integer, ByVal pintProjectID As Integer, ByVal pintUserID As Integer) As List(Of AddNewInvestor)
        Dim lstData As New List(Of AddNewInvestor)
        Dim lobjData As AddNewInvestor = Nothing
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_DE_GetLinkedInvestors")
                lentDB.AddInParameter(cmd, "FundID", DbType.Int32, pintFundID)
                lentDB.AddInParameter(cmd, "UserID", DbType.Int32, pintUserID)
                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int16, pintProjectID)
                cmd.CommandTimeout = 0
                Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                    Dim lintUserIDIndex As Int16 = reader.GetOrdinal("UserID")
                    Dim lintProjectIDIndex As Int16 = reader.GetOrdinal("ProjectID")
                    Dim lintRowNumberIndex As Int16 = reader.GetOrdinal("RowNumber")
                    Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
                    Dim lintILinkIDIndex As Int16 = reader.GetOrdinal("LinkID")
                    Dim lintFundIDIndex As Int16 = reader.GetOrdinal("FundID")
                    Dim lstrInvestorName As Int16 = reader.GetOrdinal("InvestorName")
                    Dim lstrFundName As Int16 = reader.GetOrdinal("FundName")
                    Dim lblnIsInvestor As Int16 = reader.GetOrdinal("IsInvestor")
                    Dim lblnIsManaged As Int16 = reader.GetOrdinal("IsManaged")
                    Dim lblnIsCutRow As Int16 = reader.GetOrdinal("IsCutRow")
                    Dim lstrNotes As Int16 = reader.GetOrdinal("Notes")
                    Dim lstrOrigin As Int16 = reader.GetOrdinal("Origin")
                    Dim lstrID As Int16 = reader.GetOrdinal("ID")
                    Dim lstrInvStatusCode As Int16 = reader.GetOrdinal("InvStatusCode")
                    Dim lstrName As Int16 = reader.GetOrdinal("Name")
                    Dim lintMInvestorTypeID As Int16 = reader.GetOrdinal("MInvestorTypeID")
                    Dim lstrHeadInvestor As Int16 = reader.GetOrdinal("Head Investor")
                    Dim lstrParent As Int16 = reader.GetOrdinal("Parent")
                    Dim lstrActivist As Int16 = reader.GetOrdinal("Activist")
                    Dim lstrType As Int16 = reader.GetOrdinal("Type")
                    Dim lstrStyle As Int16 = reader.GetOrdinal("Style")
                    Dim lstrTurnover As Int16 = reader.GetOrdinal("Turnover")
                    Dim lstrCity As Int16 = reader.GetOrdinal("City")
                    Dim lstrState As Int16 = reader.GetOrdinal("State")
                    Dim lstrCountry As Int16 = reader.GetOrdinal("Country")
                    Dim lstrRegion As Int16 = reader.GetOrdinal("Region")
                    Dim lstrStatus As Int16 = reader.GetOrdinal("Status")
                    Dim lintDenseRankIndex As Int16 = reader.GetOrdinal("DenseRank")
                    Dim lintFundDenseRankIndex As Int16 = reader.GetOrdinal("FundDenseRank")
                    Dim lblnIsMasterDB As Int16 = reader.GetOrdinal("IsMasterDB")
                    While reader.Read()
                        lobjData = New AddNewInvestor() With { _
                         .UserId = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintUserIDIndex)), _
                         .ProjectId = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintProjectIDIndex)), _
                         .RowNumber = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintRowNumberIndex)), _
                         .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex)), _
                         .LinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintILinkIDIndex)), _
                         .FundID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundIDIndex)), _
                         .InvestorName = DBParseUtils.GetFieldValueString(reader.GetString(lstrInvestorName)), _
                         .FundName = DBParseUtils.GetFieldValueString(reader.GetString(lstrFundName)), _
                         .IsInvestor = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lblnIsInvestor)), _
                         .IsManaged = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lblnIsManaged)), _
                         .IsCutRow = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lblnIsCutRow)), _
                         .Notes = DBParseUtils.GetFieldValueString(reader.GetString(lstrNotes)), _
                         .Origin = DBParseUtils.GetFieldValueString(reader.GetString(lstrOrigin)), _
                         .ID = DBParseUtils.GetFieldValueString(reader.GetString(lstrID)), _
                         .InvStatusCode = DBParseUtils.GetFieldValueString(reader.GetString(lstrInvStatusCode)), _
                         .Name = DBParseUtils.GetFieldValueString(reader.GetString(lstrName)), _
                         .MInvestorTypeID = DBParseUtils.GetFieldValueInt32(reader.GetByte(lintMInvestorTypeID)), _
                         .HeadInvestor = DBParseUtils.GetFieldValueString(reader.GetString(lstrHeadInvestor)), _
                         .Parent = DBParseUtils.GetFieldValueString(reader.GetString(lstrParent)), _
                         .Activist = DBParseUtils.GetFieldValueString(reader.GetString(lstrActivist)), _
                         .Type = DBParseUtils.GetFieldValueString(reader.GetString(lstrType)), _
                         .Style = DBParseUtils.GetFieldValueString(reader.GetString(lstrStyle)), _
                         .Turnover = DBParseUtils.GetFieldValueString(reader.GetString(lstrTurnover)), _
                         .City = DBParseUtils.GetFieldValueString(reader.GetString(lstrCity)), _
                         .State = DBParseUtils.GetFieldValueString(reader.GetString(lstrState)), _
                         .Country = DBParseUtils.GetFieldValueString(reader.GetString(lstrCountry)), _
                         .Region = DBParseUtils.GetFieldValueString(reader.GetString(lstrRegion)), _
                         .Status = DBParseUtils.GetFieldValueString(reader.GetString(lstrStatus)), _
                         .DenseRank = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintDenseRankIndex)), _
                         .FundDenseRank = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundDenseRankIndex)), _
                         .IsMasterDB = DBParseUtils.GetFieldValueString(reader.GetBoolean(lblnIsMasterDB)) _
                        }
                        lstData.Add(lobjData)
                    End While
                End Using
            End Using
            Return lstData
        Finally
        End Try
        'Return DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_DE_GetLinkedInvestors", {pintFundID, pintProjectID, pintUserID}).Tables(0)
    End Function

    Public Function GetChangedFund(ByVal pintLinkID As Integer, ByVal pintProjectID As Integer, ByVal pintUserID As Integer) As DataRow
        Return DataFactory.CreateDataBase().ExecuteDataSet("CR_sp_DE_GetChangedFund", {pintLinkID, pintProjectID, pintUserID}).Tables(0).Rows(0)
    End Function

    ''' <summary>
    ''' Before making updations/deletions , it is checked whether 
    ''' the record exist or not.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckRecordExistence(ByVal pstrName As String, ByVal pintId As String) As Boolean
        Return DataFactory.CreateDataBase().ExecuteScalar("CR_sp_CheckExistanceForRecord", {pstrName, pintId})
    End Function

    Public Sub CarryForwardColumn(ByVal pintSourceEntityID As Integer, ByVal pintDestEntityID As Integer, ByVal pintUserID As Integer, ByVal pintProjectID As Integer)
        DataFactory.CreateDataBase().ExecuteScalar("CR_sp_DH_CarryForwardColumn", {pintSourceEntityID, pintDestEntityID, pintUserID, pintProjectID})
    End Sub

    Public Sub MaintainFLTempTable(ByVal pstrCommand As String, ByVal ldtData As DataTable, ByVal pintProjectID As Integer, ByVal pstAllDataSourceNameCols As List(Of String), _
                                   ByVal pstAllDataSourceDateCols As List(Of String), ByVal pstDataSourceParentGroupPositionCol As List(Of String), _
                                   ByVal pstDataSourceParentGroupDateCol As List(Of String))
        Dim conn As New SqlConnection(CrystalDataHub.BLL.mstrConnectionstring)
        Dim cmd As New SqlCommand(pstrCommand, conn)
        Dim lda As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim ldtCalculatedData As New DataTable
        Dim sqlParam(0) As SqlParameter
        Try
            conn.Open()
            cmd.ExecuteNonQuery()
            Using bulk = New SqlBulkCopy(conn)
                bulk.BulkCopyTimeout = 0
                bulk.DestinationTableName = "#FLDataForSuggestions"
                bulk.ColumnMappings.Add("InvestorID", "InvestorID")
                bulk.ColumnMappings.Add("FundID", "FundID")
                For Each lCol As DataColumn In ldtData.Columns
                    If pstAllDataSourceNameCols.Contains(lCol.ColumnName) OrElse pstAllDataSourceDateCols.Contains(lCol.ColumnName) OrElse _
                    lCol.ColumnName.StartsWith(CrystalDataHub.BLL.mstrSuggestedFinalListingColPrefix) OrElse pstDataSourceParentGroupPositionCol.Contains(lCol.ColumnName) _
                    OrElse pstDataSourceParentGroupDateCol.Contains(lCol.ColumnName) Then
                        bulk.ColumnMappings.Add("[" + lCol.ColumnName + "]", "[" + lCol.ColumnName + "]")
                    End If
                Next
                bulk.WriteToServer(ldtData)
            End Using

            cmd.CommandTimeout = 0
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "CR_sp_FL_GetCalculatedSuggestions"

            sqlParam(0) = New SqlParameter("@ProjectID", pintProjectID)
            cmd.Parameters.Add(sqlParam(0))
            lda = New SqlDataAdapter(cmd)
            ldtCalculatedData = New DataTable
            lda.Fill(ldtCalculatedData)
            conn.Close()
        Finally
            cmd = Nothing
            conn = Nothing
            lda = Nothing
            ldtCalculatedData = Nothing
            sqlParam = Nothing
        End Try
    End Sub

    Public Sub MakeFlagNewFund(ByVal pintInvestorID As Integer, ByVal pintFundID As Integer, ByVal pintProjectID As Integer, ByVal pstrFlag As String)
        DataFactory.CreateDataBase().ExecuteScalar("CR_sp_FL_FlagNewFund", {pintInvestorID, pintFundID, pintProjectID, pstrFlag})
    End Sub

    Public Sub MakeFlagNewInvestor(ByVal pintInvestorID As Integer, ByVal pintProjectID As Integer, ByVal pstrFlag As String)
        DataFactory.CreateDataBase().ExecuteScalar("CR_sp_FL_FlagNewInvestor", {pintInvestorID, pintProjectID, pstrFlag})
    End Sub

    Public Sub MakeFlagChangeFund(ByVal pintInvestorID As Integer, ByVal pintOldLinkID As Integer, ByVal pintNewLinkID As Integer, ByVal pintProjectID As Integer, ByVal pstrFlag As String)
        DataFactory.CreateDataBase().ExecuteScalar("CR_sp_FL_FlagChangedFund", {pintInvestorID, pintOldLinkID, pintNewLinkID, pintProjectID, pstrFlag})
    End Sub

    'Public Sub MaintainFLTempTable(ByVal pstrCommand As String, ByVal ldtData As DataTable, ByVal pintProjectID As Integer, ByVal pstAllDataSourceNameCols As List(Of String))
    '    Dim lentDB As Database = DataFactory.CreateDataBase()
    '    Dim cmd = New SqlCommand()
    '    Try
    '        Using scope = New TransactionScope()
    '            cmd.CommandTimeout = 0
    '            cmd.CommandType = CommandType.Text
    '            cmd.CommandText = pstrCommand
    '            lentDB.ExecuteNonQuery(cmd)
    '            'cmd.ExecuteNonQuery()

    '            'BulkCopy the data in the DataTable to the temp table
    '            Using bulk = New SqlBulkCopy(CrystalDataHub.BLL.mstrConnectionstring)
    '                bulk.BulkCopyTimeout = 0
    '                bulk.DestinationTableName = "#FLDataForSuggestions"
    '                bulk.ColumnMappings.Add("InvestorID", "InvestorID")
    '                bulk.ColumnMappings.Add("FundID", "FundID")
    '                For Each lCol As DataColumn In ldtData.Columns
    '                    If pstAllDataSourceNameCols.Contains(lCol.ColumnName) Then
    '                        bulk.ColumnMappings.Add("[" + lCol.ColumnName + "]", "[" + lCol.ColumnName + "]")
    '                    End If
    '                Next
    '                bulk.WriteToServer(ldtData)
    '            End Using

    '            'cmd.CommandType = CommandType.StoredProcedure
    '            'cmd.CommandText = ""
    '            Using command As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_FL_GetCalculatedSuggestions")
    '                lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
    '                cmd.CommandTimeout = 0
    '                Dim ldtReturnData As DataTable = DataFactory.CreateDataBase().ExecuteDataSet(cmd).Tables(0)
    '                'Using reader As IDataReader = DataFactory.CreateDataBase().ExecuteReader(cmd)
    '                '    Dim lintLinkIDIndex As Int16 = reader.GetOrdinal("LinkID")
    '                '    Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
    '                '    Dim lintFundIDIndex As Int16 = reader.GetOrdinal("FundID")
    '                '    While reader.Read()
    '                '        lobjData = New LinkingDetails() With { _
    '                '         .LinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintLinkIDIndex)), _
    '                '         .InvestorID = DBParseUtils.GetValueString(reader.GetInt32(lintInvestorIDIndex)), _
    '                '         .FundID = DBParseUtils.GetValueString(reader.GetInt32(lintFundIDIndex)) _
    '                '        }
    '                '        lstData.Add(lobjData)
    '                '    End While
    '                '    Return lstData
    '                'End Using
    '            End Using

    '            'cmd.Parameters.Add(New SqlParameter("@status", CInt(status)))
    '            'cmd.ExecuteNonQuery()

    '            ''Clean up the temp table
    '            'cmd.CommandText = "DROP TABLE #Contacts"
    '            'cmd.ExecuteNonQuery()

    '            scope.Complete()
    '        End Using
    '    Finally
    '        cmd = Nothing
    '        lentDB = Nothing
    '    End Try
    'End Sub

#End Region

#Region "Full Loadup"

    Public Sub GetFullData(ByVal pintProjectID As Integer, ByRef pintDataSourcesCount As Integer, ByRef pintPreviousProjectID As Integer, _
                            ByRef pstrPPDeliveryDate As String, ByVal pblnHasSnapshot As Boolean, ByRef pdtData As DataTable, _
                            ByRef pstCurrentDataSources As IList(Of CurrentDataSources), ByRef pstPreviousDataSources As IList(Of PreviousDataSources), _
                            ByRef pstPreviousProjects As IList(Of PreviousProjects), ByRef pstrDeliveryListingToSort As String, _
                            ByRef pstDEBasicSource As IList(Of DEBasicSource), ByRef pstListingInformation As IList(Of ListingInformation), _
                            ByVal pblnCanCallCurrentListing As Boolean)
        Dim ldtPivotedSourceData As DataTable = Nothing
        Dim ldtPivotedPPData As DataTable = Nothing
        'Dim ldtInvFunds As DataTable
        Dim lentDB As Database = DataFactory.CreateDataBase()
        'Dim lstrError As String
        Dim cmd As New SqlCommand
        Dim reader As IDataReader = Nothing
        Dim keyColumn(1) As DataColumn

        Dim lobjDEBasicSource As DEBasicSource = Nothing
        Dim lobjCurrentDataSources As CurrentDataSources = Nothing
        'Dim lstRecallAddResults As IList(Of RecallAddResults) = Nothing
        Dim lobjPreviousDataSources As PreviousDataSources = Nothing
        Dim lobjPreviousProjects As PreviousProjects = Nothing
        Dim lobjListingInformation As ListingInformation = Nothing
        Dim lintLastIndex As Integer
        Try
            cmd = lentDB.GetStoredProcCommand("CR_sp_DE_GetFullData")
            lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
            lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
            lentDB.AddInParameter(cmd, "HasSnapshot", DbType.Boolean, pblnHasSnapshot)
            lentDB.AddInParameter(cmd, "CanCallCurrentListing", DbType.Boolean, pblnCanCallCurrentListing)
            'lentDB.ExecuteReader(cmd)
            cmd.CommandTimeout = 0
            reader = lentDB.ExecuteReader(cmd)

            If pblnHasSnapshot = False Then
                pdtData = New DataTable
                pdtData.Load(reader, LoadOption.OverwriteChanges)

                keyColumn(0) = pdtData.Columns("InvestorID")
                keyColumn(1) = pdtData.Columns("FundID")
                pdtData.PrimaryKey = keyColumn

                Dim lstNonPivotCols As New List(Of String)
                Dim lstPivotCols As New List(Of String)
                lstNonPivotCols.Add("InvestorID")
                lstNonPivotCols.Add("FundID")
                lstPivotCols.Add("Position")
                lstPivotCols.Add("Filing Date")
                lstPivotCols.Add("Source")
                ldtPivotedSourceData = JeffPivot(reader, "", "SourceName", lstNonPivotCols, lstPivotCols, "")
                reader.NextResult()

                lstPivotCols = New List(Of String)
                lstPivotCols.Add("Position")
                ldtPivotedPPData = JeffPivot(reader, "", "DeliveryListing", lstNonPivotCols, lstPivotCols, "")

                keyColumn(0) = ldtPivotedSourceData.Columns("InvestorID")
                keyColumn(1) = ldtPivotedSourceData.Columns("FundID")
                ldtPivotedSourceData.PrimaryKey = keyColumn

                keyColumn(0) = ldtPivotedPPData.Columns("InvestorID")
                keyColumn(1) = ldtPivotedPPData.Columns("FundID")
                ldtPivotedPPData.PrimaryKey = keyColumn

                pdtData.Merge(ldtPivotedSourceData, False, MissingSchemaAction.Add)
                pdtData.Merge(ldtPivotedPPData, False, MissingSchemaAction.Add)
                lintLastIndex = pdtData.Columns.Count - 1
                pdtData.Columns("Activist").SetOrdinal(lintLastIndex)
                pdtData.Columns("Type").SetOrdinal(lintLastIndex)
                pdtData.Columns("Style").SetOrdinal(lintLastIndex)
                pdtData.Columns("Turnover").SetOrdinal(lintLastIndex)
                pdtData.Columns("City").SetOrdinal(lintLastIndex)
                pdtData.Columns("State").SetOrdinal(lintLastIndex)
                pdtData.Columns("Country").SetOrdinal(lintLastIndex)
                pdtData.Columns("Region").SetOrdinal(lintLastIndex)
                reader.NextResult()
            End If

            Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
            Dim lintFundIDIndex As Int16 = reader.GetOrdinal("FundID")
            Dim lintSourceNameIndex As Int16 = reader.GetOrdinal("SourceName")
            Dim lintPositionIndex As Int16 = reader.GetOrdinal("Position")

            While reader.Read()
                lobjDEBasicSource = New DEBasicSource() With { _
                 .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex)), _
                 .FundID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundIDIndex)), _
                 .SourceName = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceNameIndex)), _
                 .Position = DBParseUtils.GetFieldValueString(reader.GetString(lintPositionIndex)) _
                 }
                pstDEBasicSource.Add(lobjDEBasicSource)
            End While
            reader.NextResult()

            Dim lintListingIDIndex As Int16 = reader.GetOrdinal("ListingID")
            Dim lintListingNameIndex As Int16 = reader.GetOrdinal("ListingName")
            Dim lintEntityIDIndex As Int16 = reader.GetOrdinal("EntityID")
            Dim lintESourceIDIndex As Int16 = reader.GetOrdinal("ESourceID")
            Dim lintESourceNameIndex As Int16 = reader.GetOrdinal("ESourceName")
            Dim lintSourceTypeCodeIndex As Int16 = reader.GetOrdinal("SourceTypeCode")
            Dim lintSourceDateIndex As Int16 = reader.GetOrdinal("SourceDate")
            Dim lintAccessStatusIndex As Int16 = reader.GetOrdinal("AccessStatus")
            Dim lintHasAnyDoubtsIndex As Int16 = reader.GetOrdinal("HasAnyDoubts")
            Dim lintLastUserIDIndex As Int16 = reader.GetOrdinal("LastUserID")
            Dim lintESourceCodeIndex As Int16 = reader.GetOrdinal("ESourceCode")
            Dim lintPublicFilingDateColIndex As Int16 = reader.GetOrdinal("PublicFilingDateCol")
            Dim lintPublicSourceColIndex As Int16 = reader.GetOrdinal("PublicSourceCol")
            Dim lintResearchSourceColIndex As Int16 = reader.GetOrdinal("ResearchSourceCol")
            Dim lintIsNDIndex As Int16 = reader.GetOrdinal("IsND")
            Dim lintIsNilIndex As Int16 = reader.GetOrdinal("IsNil")
            lintSourceNameIndex = reader.GetOrdinal("SourceName")
            Dim lintShortCutIndex As Int16 = reader.GetOrdinal("Shortcut")
            While reader.Read()
                lobjCurrentDataSources = New CurrentDataSources() With { _
                 .ListingID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintListingIDIndex)), _
                 .ListingName = DBParseUtils.GetFieldValueString(reader.GetString(lintListingNameIndex)), _
                 .EntityID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintEntityIDIndex)), _
                 .ESourceID = DBParseUtils.GetFieldValueByte(reader.GetByte(lintESourceIDIndex)), _
                 .ESourceName = DBParseUtils.GetValueString(reader.GetString(lintESourceNameIndex)), _
                 .SourceName = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceNameIndex)), _
                 .SourceTypeCode = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceTypeCodeIndex)), _
                 .SourceDate = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceDateIndex)), _
                 .AccessStatus = DBParseUtils.GetFieldValueString(reader.GetString(lintAccessStatusIndex)), _
                 .HasAnyDoubts = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintHasAnyDoubtsIndex)), _
                 .LastUserID = DBParseUtils.GetFieldValueInt16(reader.GetInt16(lintLastUserIDIndex)), _
                 .ESourceCode = DBParseUtils.GetFieldValueString(reader.GetString(lintESourceCodeIndex)), _
                 .PublicFilingDateCol = DBParseUtils.GetFieldValueString(reader.GetString(lintPublicFilingDateColIndex)), _
                 .PublicSourceCol = DBParseUtils.GetFieldValueString(reader.GetString(lintPublicSourceColIndex)), _
                 .ResearchSourceCol = DBParseUtils.GetFieldValueString(reader.GetString(lintResearchSourceColIndex)), _
                 .IsND = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintIsNDIndex)), _
                 .IsNIL = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintIsNilIndex)), _
            .AlphaShortcut = DBParseUtils.GetFieldValueString(reader.GetString(lintShortCutIndex)) _
                }
                pstCurrentDataSources.Add(lobjCurrentDataSources)
            End While
            reader.NextResult()

            lintEntityIDIndex = reader.GetOrdinal("EntityID")
            lintESourceNameIndex = reader.GetOrdinal("ESourceName")
            lintSourceNameIndex = reader.GetOrdinal("SourceName")
            lintSourceDateIndex = reader.GetOrdinal("SourceDate")
            lintListingNameIndex = reader.GetOrdinal("ListingName")
            lintSourceTypeCodeIndex = reader.GetOrdinal("SourceTypeCode")

            While reader.Read()
                lobjPreviousDataSources = New PreviousDataSources() With { _
                 .EntityID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintEntityIDIndex)), _
                 .ESourceName = DBParseUtils.GetValueString(reader.GetString(lintESourceNameIndex)), _
                 .SourceName = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceNameIndex)), _
                 .SourceDate = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceDateIndex)), _
                 .ListingName = DBParseUtils.GetFieldValueString(reader.GetString(lintListingNameIndex)), _
                 .SourceTypeCode = DBParseUtils.GetFieldValueString(reader.GetString(lintSourceTypeCodeIndex)) _
                 }
                pstPreviousDataSources.Add(lobjPreviousDataSources)
            End While

            reader.NextResult()

            Dim lintProjectIDIndex As Int16 = reader.GetOrdinal("ProjectID")
            lintListingIDIndex = reader.GetOrdinal("ListingID")
            Dim lintDeliveryListingIndex As Int16 = reader.GetOrdinal("DeliveryListing")
            Dim lintDeliveryDateDescIndex As Int16 = reader.GetOrdinal("DeliveryDateDescRank")

            While reader.Read()
                lobjPreviousProjects = New PreviousProjects() With { _
                 .ProjectID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintProjectIDIndex)), _
                 .ListingID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintListingIDIndex)), _
                 .DeliveryListing = DBParseUtils.GetFieldValueString(reader.GetString(lintDeliveryListingIndex)), _
                 .DeliveryDateDescRank = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintDeliveryDateDescIndex)) _
                }
                pstPreviousProjects.Add(lobjPreviousProjects)
            End While

            reader.NextResult()

            lintListingIDIndex = reader.GetOrdinal("ListingID")
            lintListingNameIndex = reader.GetOrdinal("ListingName")
            Dim lintCurrentListingNameIndex As Int16 = reader.GetOrdinal("CurrentListingName")
            Dim lintCanLoadPositionFromDBIndex As Int16 = reader.GetOrdinal("CanLoadPositionFromDB")

            While reader.Read()
                lobjListingInformation = New ListingInformation() With { _
                 .ListingID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintListingIDIndex)), _
                 .ListingName = DBParseUtils.GetFieldValueString(reader.GetString(lintListingNameIndex)), _
                 .CurrentListingName = DBParseUtils.GetFieldValueString(reader.GetString(lintCurrentListingNameIndex)), _
                 .CanLoadPositionFromDB = DBParseUtils.GetFieldValueBool(reader.GetBoolean(lintCanLoadPositionFromDBIndex)) _
        }
                pstListingInformation.Add(lobjListingInformation)
            End While

            reader.NextResult()
            While reader.Read()
                pintDataSourcesCount = reader.GetInt32(0)
                pintPreviousProjectID = reader.GetInt32(1)
                pstrPPDeliveryDate = reader.GetString(2)
                pstrDeliveryListingToSort = reader.GetString(3)
            End While
            'Catch ex As Exception
            '    cmd = Nothing
        Finally
            cmd = Nothing
            reader.Close()
            reader = Nothing
            ldtPivotedSourceData = Nothing
            ldtPivotedPPData = Nothing
            'ldtInvFunds = Nothing
            lentDB = DataFactory.CreateDataBase()
        End Try

    End Sub

    Public Sub UpdateAndGetProjectDatabase(ByRef ldtCurrentData As DataTable, ByVal pintProjectID As Integer)
        Dim ldtUpdatedData As DataTable
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Dim cmd As New SqlCommand
        Dim reader As IDataReader = Nothing
        Dim keyColumn(1) As DataColumn
        Try
            cmd = lentDB.GetStoredProcCommand("CR_sp_DE_UpdateAndGetProjectDatabase")
            lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
            lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
            cmd.CommandTimeout = 0
            reader = lentDB.ExecuteReader(cmd)
            ldtUpdatedData = New DataTable
            ldtUpdatedData.Load(reader, LoadOption.OverwriteChanges)
            keyColumn(0) = ldtUpdatedData.Columns("InvestorID")
            keyColumn(1) = ldtUpdatedData.Columns("FundID")
            ldtUpdatedData.PrimaryKey = keyColumn
            ldtCurrentData.Merge(ldtUpdatedData, False, MissingSchemaAction.Ignore)
        Finally
            ldtUpdatedData = Nothing
            lentDB = Nothing
            cmd = Nothing
            reader = Nothing
            keyColumn = Nothing
        End Try
    End Sub

    Public Function GetPreviousFullData(ByVal pintProjectID As Integer) As DataTable
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Dim cmd As New SqlCommand
        Dim lobjFLAssociatedValues As FLAssociatedValues = Nothing
        Dim lobjFLAssociatedKeys As FLAssociatedKeys = Nothing
        Dim ldtFLAssociatedData As DataTable
        Dim keyColumn(2) As DataColumn
        Try
            cmd = lentDB.GetStoredProcCommand("CR_sp_DE_GetPreviousFullData")
            lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
            lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
            cmd.CommandTimeout = 0
            ldtFLAssociatedData = lentDB.ExecuteDataSet(cmd).Tables(0)
            keyColumn(0) = ldtFLAssociatedData.Columns("InvestorID")
            keyColumn(1) = ldtFLAssociatedData.Columns("FundID")
            keyColumn(2) = ldtFLAssociatedData.Columns("DeliveryListing")
            ldtFLAssociatedData.PrimaryKey = keyColumn
            Return ldtFLAssociatedData
        Finally
            cmd = Nothing
            lobjFLAssociatedValues = Nothing
            lobjFLAssociatedKeys = Nothing
            ldtFLAssociatedData = Nothing
            lentDB = Nothing
        End Try
    End Function

    Public Sub GetCalculatedSuggestions(ByVal pintClientID As Integer, ByVal pintProjectID As Integer, ByVal pintPreviousProjectID As Integer, ByVal pstrPreviousProjectName As String, ByRef pdtData As DataTable, ByVal pintListingID As Integer, _
                                        ByVal pintInvestorID As Integer, ByVal pintLinkID As Integer, ByRef pstrSuggestedValue As String, ByVal lblnCanSetCurrentListingValue As Boolean)
        Dim ldtDataSourceParentGroupData As DataTable = Nothing
        Dim ldtSuggestedData As DataTable = Nothing
        Dim ldrData As IDataReader = Nothing
        Dim keyColumn(1) As DataColumn
        Dim lstNonPivotCols As New List(Of String)
        Dim lstPivotCols As New List(Of String)
        Try
            If pintInvestorID = -1 Then
                ldrData = DataFactory.CreateDataBase().ExecuteReader("CR_sp_FL_GetCalculatedSuggestions", {pintClientID, pintProjectID, pintListingID, pintInvestorID, pintLinkID, CommonCLL.UserID})
                lstNonPivotCols.Add("InvestorID")
                lstNonPivotCols.Add("FundID")
                lstPivotCols.Add("Position")
                lstPivotCols.Add(CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrSourceDateColSuffix.Trim)
                pdtData.AcceptChanges()

                ldtDataSourceParentGroupData = JeffPivot(ldrData, "", "ParentGroupCol", lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
                CrystalDataHub.BLL.mdlDataHubBLLCommon.SetPrimaryKey(ldtDataSourceParentGroupData)
                ldtDataSourceParentGroupData.AcceptChanges()
                pdtData.Merge(ldtDataSourceParentGroupData, False, MissingSchemaAction.Ignore)
                pdtData.AcceptChanges()

                ldrData.NextResult()

                lstPivotCols = New List(Of String)
                lstPivotCols.Add("PositionalDataSources")
                ldtSuggestedData = JeffPivot(ldrData, "", "SuggestedListing", lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
                CrystalDataHub.BLL.mdlDataHubBLLCommon.SetPrimaryKey(ldtSuggestedData)
                ldtSuggestedData.AcceptChanges()
                pdtData.Merge(ldtSuggestedData, False, MissingSchemaAction.Ignore)

                If pintPreviousProjectID = 0 AndAlso lblnCanSetCurrentListingValue Then
                    ldrData.NextResult()
                    lstPivotCols = New List(Of String)
                    lstPivotCols.Add("Position")
                    ldtSuggestedData = New DataTable
                    ldtSuggestedData = JeffPivot(ldrData, "", "CurrentListing", lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
                    CrystalDataHub.BLL.mdlDataHubBLLCommon.SetPrimaryKey(ldtSuggestedData)
                    ldtSuggestedData.AcceptChanges()
                    pdtData.Merge(ldtSuggestedData, False, MissingSchemaAction.Ignore)
                End If
            Else
                pstrSuggestedValue = DataFactory.CreateDataBase().ExecuteScalar("CR_sp_FL_GetCalculatedSuggestions", {pintClientID, pintProjectID, pintListingID, pintInvestorID, pintLinkID, CommonCLL.UserID})
            End If
        Finally
            If ldrData IsNot Nothing Then
                If ldrData.IsClosed = False Then
                    ldrData.Close()
                End If
            End If
            ldrData = Nothing
            ldtDataSourceParentGroupData = Nothing
            ldtSuggestedData = Nothing
            keyColumn = Nothing
            lstNonPivotCols = Nothing
            lstPivotCols = Nothing
        End Try
    End Sub

    Public Sub GetCurrentListingValue(ByVal pintProjectID As Integer, ByVal pstrPreviousProjectName As String, ByRef pdtData As DataTable, ByVal pintListingID As Integer, _
                                      ByVal pintInvestorID As Integer, ByVal pintLinkID As Integer, ByRef pstrAutoFillValue As String)
        Dim ldtDataSourceParentGroupData As DataTable = Nothing
        'Dim ldrData As IDataReader = Nothing
        Dim keyColumn(1) As DataColumn
        Dim lstNonPivotCols As New List(Of String)
        Dim lstPivotCols As New List(Of String)
        Dim lentDB As Database = DataFactory.CreateDataBase()
        Try
            If pintInvestorID = -1 Then
                'ldrData = DataFactory.CreateDataBase().ExecuteReader("CR_sp_FL_GetCalculatePreviousDataSourceUsed", {pintProjectID, pintListingID, pintInvestorID, pintLinkID, CommonCLL.UserID})
                Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_FL_GetCalculatePreviousDataSourceUsed")
                    lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
                    lentDB.AddInParameter(cmd, "ListingID", DbType.Int32, pintListingID)
                    lentDB.AddInParameter(cmd, "InvestorID", DbType.Int32, pintInvestorID)
                    lentDB.AddInParameter(cmd, "LinkID", DbType.Int32, pintLinkID)
                    lentDB.AddInParameter(cmd, "UserID", DbType.Int32, CommonCLL.UserID)
                    cmd.CommandTimeout = 0
                    Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                        lstNonPivotCols.Add("InvestorID")
                        lstNonPivotCols.Add("FundID")
                        lstPivotCols.Add("Position")
                        ldtDataSourceParentGroupData = JeffPivot(reader, "", "CurrentListing", lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
                        CrystalDataHub.BLL.mdlDataHubBLLCommon.SetPrimaryKey(ldtDataSourceParentGroupData)

                        pdtData.AcceptChanges()
                        ldtDataSourceParentGroupData.AcceptChanges()
                        pdtData.Merge(ldtDataSourceParentGroupData, False, MissingSchemaAction.Add)
                    End Using
                End Using
            Else
                pstrAutoFillValue = DataFactory.CreateDataBase().ExecuteScalar("CR_sp_FL_GetCalculatePreviousDataSourceUsed", {pintProjectID, pintListingID, pintInvestorID, pintLinkID, CommonCLL.UserID})
            End If
        Finally
            'If ldrData IsNot Nothing Then
            '    If ldrData.IsClosed = False Then
            '        ldrData.Close()
            '    End If
            'End If
            'ldrData = Nothing
            lentDB = Nothing
            ldtDataSourceParentGroupData = Nothing
            keyColumn = Nothing
            lstNonPivotCols = Nothing
            lstPivotCols = Nothing
        End Try
    End Sub

    Public Sub FormatDuplicatedRecords(ByVal pintProjectID As Integer, ByVal pstrPreviousProjectName As String, ByRef pdtData As DataTable)
        Dim ldtSameSourceDateUpdateData As DataTable = Nothing
        Dim ldtUpdates As DataTable = Nothing
        Dim ldtSameSourceDateDDInfoData As DataTable = Nothing
        Dim ldtDDInfo As DataTable = Nothing
        Dim ldtRegisters As DataTable = Nothing
        Dim ldtBanklists As DataTable = Nothing
        Dim ldrData As IDataReader = Nothing
        Dim keyColumn(1) As DataColumn
        Dim lstNonPivotCols As New List(Of String)
        Dim lstPivotCols As New List(Of String)
        Try
            ldrData = DataFactory.CreateDataBase().ExecuteReader("CR_sp_FL_GetFormatDuplicatedRecords", {pintProjectID})
            lstNonPivotCols.Add("InvestorID")
            lstNonPivotCols.Add("FundID")
            lstPivotCols.Add("DuplicateData")
            pdtData.AcceptChanges()

            ldtSameSourceDateUpdateData = JeffPivot(ldrData, "", CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrSameDateUpdateCount.Trim, lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
            CrystalDataHub.BLL.mdlDataHubBLLCommon.SetPrimaryKey(ldtSameSourceDateUpdateData)
            ldtSameSourceDateUpdateData.AcceptChanges()
            pdtData.Merge(ldtSameSourceDateUpdateData, False, MissingSchemaAction.Add)
            pdtData.AcceptChanges()

            ldrData.NextResult()
            ldtUpdates = JeffPivot(ldrData, "", CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrUpdateCount.Trim, lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
            CrystalDataHub.BLL.mdlDataHubBLLCommon.SetPrimaryKey(ldtUpdates)
            ldtUpdates.AcceptChanges()
            pdtData.Merge(ldtUpdates, False, MissingSchemaAction.Add)
            pdtData.AcceptChanges()

            ldrData.NextResult()
            ldtSameSourceDateDDInfoData = JeffPivot(ldrData, "", CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrSameDateDDInfoCount.Trim, lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
            CrystalDataHub.BLL.mdlDataHubBLLCommon.SetPrimaryKey(ldtSameSourceDateDDInfoData)
            ldtSameSourceDateDDInfoData.AcceptChanges()
            pdtData.Merge(ldtSameSourceDateDDInfoData, False, MissingSchemaAction.Add)
            pdtData.AcceptChanges()

            ldrData.NextResult()
            ldtDDInfo = JeffPivot(ldrData, "", CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrDDInfoCount.Trim, lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
            CrystalDataHub.BLL.mdlDataHubBLLCommon.SetPrimaryKey(ldtDDInfo)
            ldtDDInfo.AcceptChanges()
            pdtData.Merge(ldtDDInfo, False, MissingSchemaAction.Add)
            pdtData.AcceptChanges()

            ldrData.NextResult()
            ldtRegisters = JeffPivot(ldrData, "", CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrSamePositionRegisterCount.Trim, lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
            CrystalDataHub.BLL.mdlDataHubBLLCommon.SetPrimaryKey(ldtRegisters)
            ldtRegisters.AcceptChanges()
            pdtData.Merge(ldtRegisters, False, MissingSchemaAction.Add)
            pdtData.AcceptChanges()

            ldrData.NextResult()
            ldtBanklists = JeffPivot(ldrData, "", CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrSamePositionBanklistCount.Trim, lstNonPivotCols, lstPivotCols, pstrPreviousProjectName)
            CrystalDataHub.BLL.mdlDataHubBLLCommon.SetPrimaryKey(ldtBanklists)
            ldtBanklists.AcceptChanges()
            pdtData.Merge(ldtBanklists, False, MissingSchemaAction.Add)
            pdtData.AcceptChanges()
        Finally
            If ldrData IsNot Nothing Then
                If ldrData.IsClosed = False Then
                    ldrData.Close()
                End If
            End If
            ldrData = Nothing
            ldtSameSourceDateUpdateData = Nothing
            ldtUpdates = Nothing
            ldtSameSourceDateDDInfoData = Nothing
            ldtDDInfo = Nothing
            ldtRegisters = Nothing
            ldtBanklists = Nothing
            keyColumn = Nothing
            lstNonPivotCols = Nothing
            lstPivotCols = Nothing
        End Try
    End Sub

#End Region

#Region "Jeff Solution"

    Public Function SukhUnPivot(ByVal dataValues As DataTable, ByVal lstPivotCols As List(Of CurrentDataSources), ByVal pintProjectID As Integer) As DataTable
        'Public Function SukhUnPivot(ByVal dataValues As DataTable, ByVal lstPivotCols As List(Of String)) As DataTable
        Dim tmp As New DataTable()
        Dim r As DataRow
        Dim lintInvestorKeyCol As Int16
        Dim lintFundKeyCol As Int16
        Dim lintSourceNameKeyCol As Int16
        Dim lintPositionsKeyCol As Int16
        Dim lintSourceDateKeyCol As Int16
        Dim lintIsSumOfFundsKeyCol As Int16
        Dim lintFormulaKeyCol As Int16
        Dim lintClientSpecificSourceKeyCol As Int16
        Dim lintGenericSourceKeyCol As Int16
        Dim lstrPivotedColName As String = ""
        Dim lintSourceDataRowCounter As Integer = 0
        Dim ldrTraversingRow As DataRow = Nothing

        tmp.Columns.Add("UserID", GetType(Integer))
        tmp.Columns.Add("ProjectID", GetType(Integer))
        tmp.Columns.Add("InvestorID", GetType(Integer))
        tmp.Columns.Add("FundID", GetType(Integer))
        tmp.Columns.Add("EntityID", GetType(Integer))
        tmp.Columns.Add("Position", GetType(Int64))
        tmp.Columns.Add("SourceDate", GetType(Date))
        tmp.Columns.Add("IsSumOfFunds", GetType(Boolean))
        tmp.Columns.Add("Formula", GetType(String))
        tmp.Columns.Add("ClientSpecificSource", GetType(String))
        tmp.Columns.Add("GenericSource", GetType(String))

        tmp.Columns("UserID").DefaultValue = CommonCLL.UserID
        tmp.Columns("ProjectID").DefaultValue = pintProjectID

        lintInvestorKeyCol = tmp.Columns("InvestorID").Ordinal
        lintFundKeyCol = tmp.Columns("FundID").Ordinal
        lintSourceNameKeyCol = tmp.Columns("EntityID").Ordinal
        lintPositionsKeyCol = tmp.Columns("Position").Ordinal
        lintSourceDateKeyCol = tmp.Columns("SourceDate").Ordinal
        lintIsSumOfFundsKeyCol = tmp.Columns("IsSumOfFunds").Ordinal
        lintFormulaKeyCol = tmp.Columns("Formula").Ordinal
        lintClientSpecificSourceKeyCol = tmp.Columns("ClientSpecificSource").Ordinal
        lintGenericSourceKeyCol = tmp.Columns("GenericSource").Ordinal

        For Each row As DataRow In dataValues.Rows
            For Each lobjPivotCol As CurrentDataSources In lstPivotCols
                r = tmp.NewRow()
                r(lintInvestorKeyCol) = row(lintInvestorKeyCol)
                r(lintFundKeyCol) = row(lintFundKeyCol)
                r(lintSourceNameKeyCol) = lobjPivotCol.EntityID
                r(lintPositionsKeyCol) = row(lobjPivotCol.SourceName)
                r(lintSourceDateKeyCol) = row(lobjPivotCol.SourceName + " Date")
                r(lintIsSumOfFundsKeyCol) = row("~" + lobjPivotCol.SourceName)
                r(lintFormulaKeyCol) = row(lobjPivotCol.SourceName + " Formula")
                r(lintClientSpecificSourceKeyCol) = row(lobjPivotCol.SourceName + " CSSource")
                r(lintGenericSourceKeyCol) = row(lobjPivotCol.SourceName + " GSource")
                tmp.Rows.Add(r)
            Next
        Next
        Return tmp
    End Function

    Public Function JeffPivot(ByVal dataValues As IDataReader, ByVal keyColumn As String, ByVal pivotNameColumn As String, _
                                     ByVal lstNonPivotCols As List(Of String), ByVal lstPivotCols As List(Of String), _
                                     ByVal pstrPreviousProjectName As String) As DataTable
        Dim tmp As New DataTable()
        Dim r As DataRow
        Dim lstInvestorLastKey As Integer = "-1"
        Dim lstFundLastKey As Integer = "-1"
        Dim i As Integer, pNameIndex As Integer
        Dim lstrColName As String
        Dim FirstRow As Boolean = True
        Dim lintInvestorKeyCol As String = "InvestorID"
        Dim lintFundKeyCol As String = "FundID"
        Dim lstrPivotedColName As String = ""

        ' Add non-pivot columns to the data table:
        pNameIndex = dataValues.GetOrdinal(pivotNameColumn)
        For i = 0 To dataValues.FieldCount - 1
            If dataValues.GetName(i) <> pivotNameColumn AndAlso dataValues.GetName(i) <> "SourceTypeCode" AndAlso dataValues.GetName(i) <> "ESourceCode" AndAlso lstPivotCols.Contains(dataValues.GetName(i)) = False Then
                tmp.Columns.Add(dataValues.GetName(i), dataValues.GetFieldType(i))
            End If
        Next
        r = tmp.NewRow()
        ' now, fill up the table with the data:
        While dataValues.Read()
            ' see if we need to start a new row
            If dataValues(lintInvestorKeyCol) <> lstInvestorLastKey OrElse dataValues(lintFundKeyCol) <> lstFundLastKey Then
                ' if this isn't the very first row, we need to add the last one to the table
                If Not FirstRow Then
                    tmp.Rows.Add(r)
                End If
                r = tmp.NewRow()
                FirstRow = False
                ' Add all non-pivot column values to the new row:
                For i = 0 To lstNonPivotCols.Count - 1
                    r(i) = dataValues(lstNonPivotCols(i))
                Next
                lstInvestorLastKey = dataValues(lintInvestorKeyCol)
                lstFundLastKey = dataValues(lintFundKeyCol)
            End If
            ' assign the pivot values to the proper column; add new columns if needed:
            For Each lstrPivotCol As String In lstPivotCols
                lstrColName = dataValues(pNameIndex).ToString()
                If ((lstrColName.StartsWith(pstrPreviousProjectName) OrElse lstrColName.StartsWith("~" + pstrPreviousProjectName)) AndAlso pstrPreviousProjectName IsNot String.Empty AndAlso lstrPivotCol <> "Position") Then Continue For
                If lstrPivotCol = "Position" OrElse lstrPivotCol = "PositionalDataSources" OrElse lstrPivotCol = "DuplicateData" Then
                    lstrPivotedColName = lstrColName
                ElseIf lstrPivotCol = "~" Then
                    lstrPivotedColName = lstrPivotCol + lstrColName
                Else
                    lstrPivotedColName = lstrColName + " " + lstrPivotCol
                End If
                If Not tmp.Columns.Contains(lstrPivotedColName) Then
                    tmp.Columns.Add(lstrPivotedColName, dataValues.GetFieldType(dataValues.GetOrdinal(lstrPivotCol)))
                End If
                r(lstrPivotedColName) = dataValues(lstrPivotCol)
            Next
        End While
        ' add that final row to the datatable:
        If FirstRow = False Then
            tmp.Rows.Add(r)
        End If
        ' and that's it!
        'RemoveUnnecessaryAssociatedFields(tmp, lstActiveDataSources)
        Return tmp
    End Function

    'Private Sub RemoveUnnecessaryAssociatedFields(ByRef ldtUpdatedData As DataTable, ByVal lstActiveDataSources As List(Of CurrentDataSources))
    '    'For Each lCol As DataColumn In ldtCurrentData-
    '    For Each lobjColName As CurrentDataSources In lstActiveDataSources
    '        If lobjColName.SourceTypeCode <> SourceTypeCode.PBC.ToString Then
    '            ldtUpdatedData.Columns.Remove(lobjColName.SourceName + CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrSourceColSuffix)
    '            ldtUpdatedData.Columns.Remove(lobjColName.SourceName + CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrFilingDateColSuffix)
    '        End If
    '        If lobjColName.SourceTypeCode <> SourceTypeCode.RSH.ToString Then
    '            ldtUpdatedData.Columns.Remove(lobjColName.SourceName + CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrClientSpecificSourceColSuffix)
    '            ldtUpdatedData.Columns.Remove(lobjColName.SourceName + CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrGenericSourceColSuffix)
    '            ldtUpdatedData.Columns.Remove(lobjColName.SourceName + " RSource")
    '        End If
    '        If CrystalDataHub.BLL.mdlDataHubBLLCommon.marrEditableSourceTypes.Contains(lobjColName.SourceTypeCode) = False Then
    '            ldtUpdatedData.Columns.Remove(lobjColName.SourceName + CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrFormulaColSuffix)
    '            ldtUpdatedData.Columns.Remove(CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrSOFColPrefix + lobjColName.SourceName)
    '        End If
    '        If CrystalDataHub.BLL.mdlDataHubBLLCommon.marrDateSuppertedSourceTypes.Contains(lobjColName.ESourceCode) = False Then
    '            ldtUpdatedData.Columns.Remove(lobjColName.SourceName + CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrSourceDateColSuffix)
    '        End If
    '    Next

    '    For Each lobjColName As CurrentDataSources In lstActiveDataSources
    '        If lobjColName.SourceTypeCode = SourceTypeCode.RSH.ToString Then
    '            If ldtUpdatedData.Columns.Contains(lobjColName.SourceName + " RSource") Then
    '                ldtUpdatedData.Columns(lobjColName.SourceName + " RSource").ColumnName = lobjColName.SourceName + CrystalDataHub.BLL.mdlDataHubBLLCommon.mstrSourceColSuffix
    '            End If
    '        End If
    '    Next

    'End Sub

    'Public Shared Function JeffPivot(ByVal dataValues As IDataReader, ByVal keyColumn As String, ByVal pivotNameColumn As String, ByVal pivotValueColumn As String) As DataTable
    '    Dim tmp As New DataTable()
    '    Dim r As DataRow
    '    Dim lstInvestorLastKey As String = "//dummy//"
    '    Dim lstFundLastKey As String = "//dummy//"
    '    Dim i As Integer, pValIndex As Integer, pNameIndex As Integer
    '    Dim s As String
    '    Dim FirstRow As Boolean = True
    '    Dim lintInvestorKeyCol As String = "InvestorID"
    '    Dim lintFundKeyCol As String = "FundID"
    '    ' Add non-pivot columns to the data table:
    '    pValIndex = dataValues.GetOrdinal(pivotValueColumn)
    '    pNameIndex = dataValues.GetOrdinal(pivotNameColumn)
    '    For i = 0 To dataValues.FieldCount - 1
    '        If i <> pValIndex AndAlso i <> pNameIndex Then
    '            tmp.Columns.Add(dataValues.GetName(i), dataValues.GetFieldType(i))
    '        End If
    '    Next
    '    r = tmp.NewRow()
    '    ' now, fill up the table with the data:
    '    While dataValues.Read()
    '        ' see if we need to start a new row
    '        If dataValues(lintInvestorKeyCol).ToString() <> lstInvestorLastKey AndAlso dataValues(lintFundKeyCol).ToString() <> lstFundLastKey Then
    '            ' if this isn't the very first row, we need to add the last one to the table
    '            If Not FirstRow Then
    '                tmp.Rows.Add(r)
    '            End If
    '            r = tmp.NewRow()
    '            FirstRow = False
    '            ' Add all non-pivot column values to the new row:
    '            For i = 0 To dataValues.FieldCount - 3
    '                r(i) = dataValues(tmp.Columns(i).ColumnName)
    '            Next
    '            lstInvestorLastKey = dataValues(lintInvestorKeyCol).ToString()
    '            lstFundLastKey = dataValues(lintFundKeyCol).ToString()
    '        End If
    '        ' assign the pivot values to the proper column; add new columns if needed:
    '        s = dataValues(pNameIndex).ToString()
    '        If Not tmp.Columns.Contains(s) Then
    '            tmp.Columns.Add(s, dataValues.GetFieldType(pValIndex))
    '        End If
    '        r(s) = dataValues(pValIndex)
    '    End While
    '    ' add that final row to the datatable:
    '    tmp.Rows.Add(r)
    '    ' Close the DataReader
    '    'dataValues.Close()
    '    ' and that's it!
    '    Return tmp
    'End Function

    'Public Shared Function JeffPivot(ByVal dataValues As IDataReader, ByVal keyColumn As String, ByVal pivotNameColumn As String, ByVal pivotValueColumn As String) As DataTable
    '    Dim tmp As New DataTable()
    '    Dim r As DataRow
    '    Dim LastKey As String = "//dummy//"
    '    Dim i As Integer, pValIndex As Integer, pNameIndex As Integer
    '    Dim s As String
    '    Dim FirstRow As Boolean = True
    '    ' Add non-pivot columns to the data table:
    '    pValIndex = dataValues.GetOrdinal(pivotValueColumn)
    '    pNameIndex = dataValues.GetOrdinal(pivotNameColumn)
    '    For i = 0 To dataValues.FieldCount - 1
    '        If i <> pValIndex AndAlso i <> pNameIndex Then
    '            tmp.Columns.Add(dataValues.GetName(i), dataValues.GetFieldType(i))
    '        End If
    '    Next
    '    r = tmp.NewRow()
    '    ' now, fill up the table with the data:
    '    While dataValues.Read()
    '        ' see if we need to start a new row
    '        If dataValues(keyColumn).ToString() <> LastKey Then
    '            ' if this isn't the very first row, we need to add the last one to the table
    '            If Not FirstRow Then
    '                tmp.Rows.Add(r)
    '            End If
    '            r = tmp.NewRow()
    '            FirstRow = False
    '            ' Add all non-pivot column values to the new row:
    '            For i = 0 To dataValues.FieldCount - 3
    '                r(i) = dataValues(tmp.Columns(i).ColumnName)
    '            Next
    '            LastKey = dataValues(keyColumn).ToString()
    '        End If
    '        ' assign the pivot values to the proper column; add new columns if needed:
    '        s = dataValues(pNameIndex).ToString()
    '        If Not tmp.Columns.Contains(s) Then
    '            tmp.Columns.Add(s, dataValues.GetFieldType(pValIndex))
    '        End If
    '        r(s) = dataValues(pValIndex)
    '    End While
    '    ' add that final row to the datatable:
    '    tmp.Rows.Add(r)
    '    ' Close the DataReader
    '    dataValues.Close()
    '    ' and that's it!
    '    Return tmp
    'End Function
#End Region

End Class
